tips for nested IFs?

[merlin777]

I have to nest about 5 IF statements and the flamings things are really difficult to follow.

Does anyone have any tips to make it less confusing - work backwards maybe?

 

[shg]

There are usually better ways to do things than deeply-nested IFs, but as you would expect, it kinda depends on what you're trying to do.

 

[mole999]

build each one, test they work, then build the master using the outcomes that will be achieved first most often (if possible), add two together, and test etc

 

[welshgasman]

Indent the code?

 

[merlin777]

Indent the code?

I think that would be for VBA? I'm not sure how I would do that for a formula in a cell.

 

[welshgasman]

Ahh, sorry. I *thought* you were talking about VBA.
As mentioned previously, perhaps there is a better way with VLOOKUP, MATCH, INDEX that sought of thing, which others would know more about.?
Remember, there is nothing to stop you writing your own function that would have those nested IFs and return a value if other functions cannot be combined easily

Perhaps if you explained what the nested ifs are meant to do with cell positions as well, someone might be able to offer a better solution.?

FWIW, if I had to do them that way, I'd work from the inside out?

 

[FDibbins]

is this really a nested IF? or is it an IF with 5 different options?

Nested IF...
IF(male, If(brunette, IF(older than 20, IF(speaks English, IF(less than 6ft tall, do this, do that)

5 different options...
=if(A1=1, "a", if(a1=2, "b", if(a1=3,"c", if(a1=4,"d", if(a1=5, "e",""))))

For the 1st 1, *maybe???* use AND()
for the 2nd, use a table and vlookup()

 

[merlin777]

There are usually better ways to do things than deeply-nested IFs, but as you would expect, it kinda depends on what you're trying to do.

Let me have a try....

I have a staff type (CW, FL or STAFF) , a date, and a shift start time and end time from which I calculate a duration. Shifts may start before midnight and finish after.

My task is to calculate the hours to be paid for a shift, with the following:

Only contact workers (CW) attract overtime.

overtime is paid for:
xmas day, all the hours worked for the shift at 2T (possibly other days at 2T TBA)
any bank holiday, all the hours worked on that day are at 1.5T
any hours between 00:00 and 06:00 at 1.5T

Overtime does not compound i.e. overtime is only applied to one of the above, in order of precedence as above.

For each shift I want to calculate the effective number of hours to be paid and then I can apply a pay rate for each individual.

Here are screen shots. One is my main table and the other shows part of a sheet called ADMIN where I put a list of bank holiday and xmas dates and the hours that overtime applies. One table is called 'xmasdays' and the other 'OTDATES'. I've done it in tables like this because dates will change from year to year and it gives my users flexibility to specify extra days and premium payment times.

https://www.dropbox.com/s/gij0tkepkko96yr/Screenshot 2016-05-30 20.12.36.png?dl=0
https://www.dropbox.com/s/jqigxumfc6orbyx/Screenshot 2016-05-30 20.11.42.png?dl=0

This is possibly the most complicated thing I've tried so any advice would be gratefully received!

 

[merlin777]

is this really a nested IF? or is it an IF with 5 different options?

Nested IF...
IF(male, If(brunette, IF(older than 20, IF(speaks English, IF(less than 6ft tall, do this, do that)

5 different options...
=if(A1=1, "a", if(a1=2, "b", if(a1=3,"c", if(a1=4,"d", if(a1=5, "e",""))))

For the 1st 1, *maybe???* use AND()
for the 2nd, use a table and vlookup()

I think its nested IFs i.e.:

If it's a contract worker, check if they worked on xmas day, if they did then pay double time, if they didn't then check if they worked on a bank holiday, if they did then pay time and a half, if they didn't then check what hours they worked between midnight and 6am and pay those hours at time and a half, if none of these conditions are true then just pay worked hours at time.

I think that's nested?

 

[thisoldman]

If it's a contract worker, check if they worked on xmas day, if they did then pay double time, if they didn't then check if they worked on a bank holiday, if they did then pay time and a half, if they didn't then check what hours they worked between midnight and 6am and pay those hours at time and a half, if none of these conditions are true then just pay worked hours at time.

I think something like this may work:

=(HoursWorked + IF(AND(Worker = "CW", Date <> Christmas, Date <> Holiday), NightHours * 0.5, 0)) * IF(Worker <> CW, 1, IF(Date = Christmas, 2, IF(Date = Holiday, 1.5, 1))) * BasePay

First IF, adjustment for night shift hours
If the person is a "CW" and it's not Christmas and it's not a holiday, then add half the night shift hours to the total hours worked. That gives you time-and-a-half for the non-Christmas, non-Holiday premium hours.

Second IF, check eligibility for overtime
If the person is not a "CW", the base pay multiplier is 1.

Third IF, check for Christmas
The person is a "CW".
If it's Christmas, multiply the base pay by 2.

Fourth IF, check for holiday
The person is a "CW". It is not Christmas.
If it's a holiday then multiply the base pay by 1.5.

Else, no overtime
The person is a "CW". It is not Christmas. It is not a holiday.
The base pay multiplier is 1.