Nested IFs and error message "uses more levels of nesting than allowed"

Hi everyone

I'm having a nightmare with nested ifs. Basically I have a spreadsheet capturing rota timing. To make easier for users they enter in clock format timing i.e. if someone starts at 9:15am they select 09:15 rather than a decimalised number of 9.25. When it comes to working out the total time worked and I start to subtract start, finish and break times I get decimalised answers e.g.
start = 09:15, end = 11:30, break = 0:45 so they worked an hour and a half but if I subtract start and break time from finish time I get 1.7 hrs worked.
So I've created a nested if statement to cover all funnies that might fall out like .7 should be .5 in this example. Only thing is when I add in a pre-check IF (to see if they are on holiday) if says I've too many nests..

I have looked a various other responses to this query from other users but I cannot seem to get them to work for me. Any thoughts greatly appreciated.

Here is code so formula..
=IF(C80 = "Hol", "0", if(RIGHT(ROUND(D84-C84-E84,2),2)="55",(D84-C84-E84)-0.3,IF(RIGHT(ROUND(D84-C84-E84,2),2)="85",(D84-C84-E84)-0.1,IF(RIGHT(ROUND(D84-C84-E84,2),2)="15",(D84-C84-E84)+0.1,IF(RIGHT(ROUND(D84-C84-E84,2),2)=".3",(D84-C84-E84)+0.2,IF(RIGHT(ROUND(D84-C84-E84,2),2)=".7",(D84-C84-E84)-0.2,IF(RIGHT(ROUND(D84-C84-E84,2),2)="45",(D84-C84-E84)+0.3,IF(RIGHT(D84-C84-E84,2)=".4", (D84-C84-E84)-0.4,(D84-C84-E84)))))))))

First of all, you don't need the quotes around the 0 if C80 = "Hol" - that will turn it into a text value.

Secondly, you can avoid nesting of IF statements by using this approach:

=IF(condition1,result1,0) + IF(condition2,result2,0) + IF(condition3,result3,0) + IF(condition4,result4,0) + and so on.

Hope this helps.

Pete

Ooh Thanks Pete - I'll give that a shot now

Sadly I cannot get it to work. If I am on the right lines I think =IF(condition1,result1,0) + IF(condition2,result2,0) means that it will look for an answer from both Ifs. But I only want one. I've used this to test it:
=IF(C83="Hol",0,0)+IF(RIGHT(ROUND(D83-C83-E83,2),2)=".3", (D83-C83-E83)+0.2, (D83-C83-E83))
if I enter in hours it's fine but if select Hol then it says #Value!

Basically if someone is on holiday then I don't need to calculate hours but if they aren't then I need those horrible if's where only 1 is right.

Am I doing something wrong?

In the structure that I gave you, every IF is evaluated, but they will return 0 to the sum if they do not satisfy the condition. Note that you are either adding or subtracting some value from the expression D83-C83-E83, so you could start off with:

=(D83-C83-E83) + IF(C83="Hols",-(D83-C83-E83),0) + IF(RIGHT(ROUND(D83-C83-E83,2),2)=".3",0.2, 0) + IF(RIGHT(ROUND(D83-C83-E83,2),2)="55",-0.3,0) + ...

So here each IF statement will return either the value to be added to or subtracted from the expression, or zero.

Hope this helps.

Pete

use( And )function and (If) function...

thanks Pete - I'll try that too. I'm confident based on what you said it will work though. Really appreciate the help

Originally Posted by Pete_UK

In the structure that I gave you, every IF is evaluated, but they will return 0 to the sum if they do not satisfy the condition. Note that you are either adding or subtracting some value from the expression D83-C83-E83, so you could start off with:

=(D83-C83-E83) + IF(C83="Hols",-(D83-C83-E83),0) + IF(RIGHT(ROUND(D83-C83-E83,2),2)=".3",0.2, 0) + IF(RIGHT(ROUND(D83-C83-E83,2),2)="55",-0.3,0) + ...

So here each IF statement will return either the value to be added to or subtracted from the expression, or zero.

Hope this helps.

Pete