Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
12/03/201014/03/20100.03£60.11£14.70£45.41£45.41 £64.30£30.00£12.86
27/09/201027/09/20100.01£20.04£5.37£14.67£14.67 £65.45£27.86£3.98
02/07/201103/07/20110.02£25.86£9.49£16.37£16.37 £67.50£34.29£9.80
11/03/201311/03/20130.01£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.
 
Upvote 0
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 ??
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top