Alternative to Nested IF for Hours of Pay

[marvin jones]

Hello There,

I have a massive IF nested formula that returns a value of 8 hours worked depending on the sum of other cells. So,

L20: IF the SUM of Q20, U20, Y20 equals 1 hour then return 7 hours | IF the SUM of Q20, U20, Y20 equals 2 hours then return 6 hours
L21: IF the SUM of Q21, U21, Y21 equals 1 hour then return 7 hours | IF the SUM of Q21, U21, Y21 equals 2 hours then return 6 hours

This is OK, but i need the hours to be split into quarters too. So,

L20: IF the SUM of Q20, U20, Y20 equals 1.25 hours then return 6.75 hours | IF the SUM of Q20, U20, Y20 equals 2.25 hours then return 5.75 hours
L21: IF the SUM of Q21, U21, Y21 equals 1.50 hour then return 6.50 hours | IF the SUM of Q21, U21, Y21 equals 0.50 hours then return 7.50 hours

This calculation works out a time for each employee each day, so a nested IF statement summing this up will just be too much.

Is there an easier way please?

Thank you,

 

[Saba Sabaratnam]

You could create a lookup table this and use your sum value to lookup result using VLOOKUP or XLOOKUP (365).

Kind regards

Saba

 

[Saba Sabaratnam]

if you do not want to use separate table, you can use array approach as showing below.

=VLOOKUP(sum(Q20, U20, Y20),{1,7;1.25,6.75;2.25,5.75;1.5,6.5;0.5,7.5},2,FALSE)

Kind regards

Saba

 

[Alex Blakenburg]

Am I missing something here but looking at Saba's lookup table, do all the calculations boils down to being:-
=8 - Sum(Q20,U20,Y20)