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?

 

[merlin777]

I can see what you're doing with that although I don't fully understand it! In your formula you use 'christmas' and 'holiday' but they are both lists rather than a single value in a cell so would that still work?

Also, how do i find the number of hours between midnight and six to multiply by 0.5?

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.

 

[thisoldman]

I'm sorry for not replying sooner—an important project intervened. And I'll be away from computers and the internet for the next two weeks.

Without some specifics from you, it's difficult come up with a solution. People need to see exactly how your spreadsheet is set out. I will suggest that you break the problem down into parts—use helper columns. "There are usually better ways to do things than deeply-nested IFs."

To get help, you should post an example of your current spreadsheet. Please make sure you mention whether your dates and times are formatted as Excel dates and times or if they are text dates and times.

I apologize for not being able to offer any other help; I just don't have the time right now.

 

[merlin777]

No problem - thanks for your help.

and you are right. I broke it up into separate columns and got those working first and then substituting them into one formula was much easier.

I'm sorry for not replying sooner—an important project intervened. And I'll be away from computers and the internet for the next two weeks.

Without some specifics from you, it's difficult come up with a solution. People need to see exactly how your spreadsheet is set out. I will suggest that you break the problem down into parts—use helper columns. "There are usually better ways to do things than deeply-nested IFs."

To get help, you should post an example of your current spreadsheet. Please make sure you mention whether your dates and times are formatted as Excel dates and times or if they are text dates and times.

I apologize for not being able to offer any other help; I just don't have the time right now.