Excel nested IF formula

Question

I want to set a formula that incorporates my school's attendance policy:

3 tardies = 1 unexcused absence
6 tardies = 2 unexcused absences
and so on

The cell must reference not only the cell with the Tardy count, but also the attendance worksheet in the same workbook, and must add the two values. For example, if the attendance page shows 1 unexcused absence and the tardy count shows 7 tardies, the formula needs to return 3.

This is what I've got:

 =SUM(Attendance!O9+(IF(J4>=3,1,IF(J4>=6,2,IF(J4>=9,3,IF(J4>=12,4,))))))

It works for 4 tardies and 1 unexcused absence, but continues to return 2 for 7 tardies and 1 unexcused absence.

What am I missing?

David Zemens · Accepted Answer · 2013-06-13 16:31:59Z

4

Much easier than using If/Then:

=Attendance!O9+(Int(J4/3))

The Int function returns the floor integer of the calculation, J4/3. So, if J4 = 7, the result is 2. If J4=9, the result is 3, etc.

answered Jun 13, 2013 at 16:31

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jerry Over a year ago

+1 Yup, and even takes care of things like 12, 15 and so on without requiring more nested ifs :)

Emma · Accepted Answer · 2013-06-13 16:31:32Z

2

Your if statements are backwards you should check for bigger numbers first in this case otherwise the first if is always true. so 7 is falling true on >= 3 therefore you get 1 + 1 = 2

EDIT - a better way to do it would be to take your J4 divide it by 3 and return the value without the remainder

QUOTIENT(J4, 3)

edited Jun 13, 2013 at 16:31

answered Jun 13, 2013 at 16:25

Emma

1671 silver badge9 bronze badges

2 Comments

David Zemens Over a year ago

+1, for good explanation of why OP's formula was failling. Also, for showing another more reliable way to do this, instead of If/Then logic.

user2483018 Over a year ago

I never knew about the quotient function before. Thanks!

Pliskin · Accepted Answer · 2013-06-13 16:36:05Z

1

The if part of your formula should be as follows

IF(J4<3,0,(IF(J4<6,1,(IF(J4<9,2,IF(J4<12,3,4))))))

Bear in mind that this formulae limits you to a maximum of 4 unexcused absences for tardies.

A better solution would be to use the QUOTIENT formula

= Attendance!O9 + QUOTIENT(J4,3)

edited Jun 13, 2013 at 16:36

answered Jun 13, 2013 at 16:26

Pliskin

1,29910 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel nested IF formula

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related