# 14 Nested IF Statements

#### Chris Waller

##### Board Regular
I have inherited a formula from an ex-colleague that contains approximately 14 nested IF statements.

I have read somewhere today that you cannot use more than 7.

I am using Excel 2002 and I would be grateful if someone could tell me how I can adjust the formula to ensure that it works correctly. I do have access to a different system and when I compare the results, out of 18 sets of figures, 5 of them are incorrect, by 1 to 5 pence.

The only solution I can come up with is that the part of the formula that is supposed to be rounding, is not working correctly. Any help you can give would be greatly appreciated. Please find the formula below. TIA

=IF(M5="","",IF(C5>0,IF((E5>M5)*AND(M5>F5),M5-F5,IF((M5>E5)*AND(M5>F5),G5,IF((F5>E5)*AND(E5>M5),0,IF((M5>F5)*AND(F5>E5),E5-F5,IF((F5>M5)*AND(M5>E5),E5-M5,""&IF(M5="","",0)))))),IF((E5>R5)*AND(R5>F5),ROUNDUP(M5-((F5/D5)*7),2),IF((R5>E5)*AND(E5>F5),ROUNDUP(((E5-F5)/D5)*7,2),IF((R5>F5)*AND(F5>E5),ROUNDUP(((E5-F5)/D5)*7,2),IF((F5>E5)*AND(E5>R5),0,IF((F5>(M5/7)*D5)*AND(M5>(E5/D5)*7),ROUNDUP(((E5/D5)*7)-M5,2),IF(M5="","",0))))))))

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### Active Member
Hi,
is it possible to present an suitable example and expected result... ?? Best regards.

#### Chris Waller

##### Board Regular
 12/03/2010 14/03/2010 0 3 £60.11 £14.70 £45.41 £45.41 £64.30 £30.00 £12.86 27/09/2010 27/09/2010 0 1 £20.04 £5.37 £14.67 £14.67 £65.45 £27.86 £3.98 02/07/2011 03/07/2011 0 2 £25.86 £9.49 £16.37 £16.37 £67.50 £34.29 £9.80 11/03/2013 11/03/2013 0 1 £12.93 £7.49 £5.44 £5.44 £71.70 £19.27 £2.76

<COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" span=2 width=68><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1060" width=29><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" span=6 width=57><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" span=2 width=57><TBODY>
</TBODY>

The figures highlighted in yellow are incorrect. They should be according to our other system be as follows £30.01, £27.91, £34.31 and £19.28. If there are any other problems, please re-post.

##### Active Member
OK,
now is better I have one more Question. Why the values £30.01, £27.91, £34.31 and £19.28. should be instead of Your yellow values ? Would You like to explain this ? Can you give a suitable formula/algorithm ??

#### pjmorris

##### Well-known Member
Hi Chris,

Just some thoughts which might help:

1. The construction of the tests, such as this (E5>M5)*AND(M5>F5) is unusual. Because of the way Excel works the AND in that statement is actually irrelevant and the statement could just as easily be written (E5>M5)*(M5>F5). However the right (conventional) way to have written it would be AND(E5 > M5, M5 > F5). They all produce the same result, but the final version is probably easier to read and understand.
2. It might be worth trying to step through the formula. Click on a cell with the formula in it and Press F9 this should let you see how the formula is evaluated step by step.
3. It does occur to me that this formula is likely to be a rather overcomplicated and as hurgadion suggested it would help if you could describe the logic which needs to be implemented. Unfortunately your post doesnt provide sufficient information, for example what data is in Column R? You need to describe where the data shown above fits on the table otherwise it is impossible to make sense of the formula.

Hope this helps (a bit).

Regards

Peter

#### Chris Waller

##### Board Regular
Yes it is.

Column R contains the following formula.

=IF(M5="","",IF(D5=0,M5,ROUNDUP(((M5/7)*D5),2)))

This formula is working out an over/under payment, which is in M5 divide by 7 days of the week, to find the daily rate times by the number of days.

I hope this helps.

#### Chris Waller

##### Board Regular
Peter,

I did try the formula with AND(E5 > M5, M5 > F5) as you suggested and it does work fine. Since then I have tried various combinations, but for some strange reason out of the 18 cells only 5 of them are showing any kind of discrepancy and it is these that are causing concern. This is why I suspect that the latter half of the IF statements are not working. I hope this helps.

As I mentioned I have inherited this formula so I am not clear what it is trying to do. I know what the end result should be, but not how to get there.

#### pjmorris

##### Well-known Member
Hi Chris

I need to know where the data table you provided above is located: what is the address of the cell containing the date 12/3/2010 and are all the cells then contiguous?

How do you know the formula is giving you the wrong answer and not the alternative system you refer too?

Regards

#### Chris Waller

##### Board Regular
Hi Chris

I need to know where the data table you provided above is located: what is the address of the cell containing the date 12/3/2010 and are all the cells then contiguous?

How do you know the formula is giving you the wrong answer and not the alternative system you refer too?

Regards

Peter, the cell containing the date 12/03/2010 is in A5, all the others are contiguous as you mentioned.

The other system might be wrong, and I cannot prove which is wrong the other system or Excel, but reading that you cannot nest more than 7 if statements in Excel does not give me any reassurance that Excel is working it out correctly either, and I am more inclined to think it is a problem with Excel not rounding correctly rather than the other system. Once I am assured that Excel is doing it right, I can start looking deeper into the other system.

Replies
10
Views
227
Replies
13
Views
215
Replies
1
Views
58
Replies
4
Views
101
Replies
2
Views
121

1,195,616
Messages
6,010,726
Members
441,565
Latest member
menangterus556

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back