14 Nested IF Statements

[Chris Waller]

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))))))))

 

[hurgadion]

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

 

[Chris Waller]

[TABLE="width: 760"]
<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>[TR]
[TD="class: xl32, width: 68, bgcolor: transparent, align: right"]12/03/2010[/TD]
[TD="class: xl32, width: 68, bgcolor: transparent, align: right"]14/03/2010[/TD]
[TD="class: xl33, width: 35, bgcolor: transparent"]0.0[/TD]
[TD="class: xl34, width: 29, bgcolor: transparent"]3[/TD]
[TD="class: xl35, width: 78, bgcolor: transparent"]£60.11[/TD]
[TD="class: xl43, width: 72, bgcolor: transparent"]£14.70[/TD]
[TD="class: xl35, width: 57, bgcolor: transparent"]£45.41[/TD]
[TD="class: xl35, width: 74, bgcolor: transparent"]£45.41[/TD]
[TD="class: xl36, width: 57, bgcolor: transparent"] [/TD]
[TD="class: xl35, width: 57, bgcolor: transparent"] [/TD]
[TD="class: xl35, width: 57, bgcolor: transparent"] [/TD]
[TD="class: xl35, width: 57, bgcolor: transparent"] [/TD]
[TD="class: xl35, width: 57, bgcolor: transparent"]£64.30[/TD]
[TD="class: xl44, width: 57, bgcolor: yellow"]£30.00[/TD]
[TD="class: xl35, width: 72, bgcolor: transparent"]£12.86[/TD]
[TD="class: xl37, width: 57, bgcolor: transparent"] [/TD]
[TD="class: xl37, width: 57, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: transparent, align: right"]27/09/2010[/TD]
[TD="class: xl32, bgcolor: transparent, align: right"]27/09/2010[/TD]
[TD="class: xl33, bgcolor: transparent"]0.0[/TD]
[TD="class: xl34, bgcolor: transparent"]1[/TD]
[TD="class: xl35, bgcolor: transparent"]£20.04[/TD]
[TD="class: xl43, bgcolor: transparent"]£5.37[/TD]
[TD="class: xl35, bgcolor: transparent"]£14.67[/TD]
[TD="class: xl35, bgcolor: transparent"]£14.67[/TD]
[TD="class: xl36, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"]£65.45[/TD]
[TD="class: xl44, bgcolor: yellow"]£27.86[/TD]
[TD="class: xl35, bgcolor: transparent"]£3.98[/TD]
[TD="class: xl37, bgcolor: transparent"] [/TD]
[TD="class: xl37, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: transparent, align: right"]02/07/2011[/TD]
[TD="class: xl32, bgcolor: transparent, align: right"]03/07/2011[/TD]
[TD="class: xl33, bgcolor: transparent"]0.0[/TD]
[TD="class: xl34, bgcolor: transparent"]2[/TD]
[TD="class: xl35, bgcolor: transparent"]£25.86[/TD]
[TD="class: xl43, bgcolor: transparent"]£9.49[/TD]
[TD="class: xl35, bgcolor: transparent"]£16.37[/TD]
[TD="class: xl35, bgcolor: transparent"]£16.37[/TD]
[TD="class: xl36, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"]£67.50[/TD]
[TD="class: xl44, bgcolor: yellow"]£34.29[/TD]
[TD="class: xl35, bgcolor: transparent"]£9.80[/TD]
[TD="class: xl37, bgcolor: transparent"] [/TD]
[TD="class: xl37, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: transparent, align: right"]11/03/2013[/TD]
[TD="class: xl32, bgcolor: transparent, align: right"]11/03/2013[/TD]
[TD="class: xl33, bgcolor: transparent"]0.0[/TD]
[TD="class: xl34, bgcolor: transparent"]1[/TD]
[TD="class: xl35, bgcolor: transparent"]£12.93[/TD]
[TD="class: xl43, bgcolor: transparent"]£7.49[/TD]
[TD="class: xl35, bgcolor: transparent"]£5.44[/TD]
[TD="class: xl35, bgcolor: transparent"]£5.44[/TD]
[TD="class: xl36, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"] [/TD]
[TD="class: xl35, bgcolor: transparent"]£71.70[/TD]
[TD="class: xl44, bgcolor: yellow"]£19.27[/TD]
[TD="class: xl35, bgcolor: transparent"]£2.76[/TD]
[TD="class: xl37, bgcolor: transparent"] [/TD]
[TD="class: xl37, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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.

 

[hurgadion]

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]

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

 

[Akashwani]

Hi Chris,

Is this the same problem as this one?...

http://www.mrexcel.com/forum/excel-questions/696322-rounding-formula.html

Ak

 

[Chris Waller]

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]

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]

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]

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.