Forum Discussion
Solved
Help with If function with 12 conditions
=IF(G98<=1,"0",IF(G98>=1,"40",IF(G98>=2,"48",IF(G98>=3,"56",IF(G98>=4,"64",IF(G98>=5,"80",IF(G98>=6,"88",IF(G98>=7,"96",IF(G98>=8,"104",IF(G98>=9,"112",IF(G98>=10,"120",IF(G98>10,"120")))))))))))) ...
I think that IF(G98<=1,"0", IF(G98>=1,"40" need to be corrected to =IF(G98<1,"0",IF(G98>=1,"40".
You may write the formula as below.
=INDEX({0,40,48,56,64,80,88,96,104,112,120},MATCH(G98,{0,1,2,3,4,5,6,7,8,9,10}))
1) Why does your formula return text values instead of numbers?
2) Why is there a jump form 64 to 80?
Without the jump, you could use the following in H3 and fill down:
=IF(G3<1,0,INT(G3)*8+32)
With the jump:
=IF(G3<1,0,(INT(G3)+(G3>=5))*8+32)
- The returning of text? Do you mean the False that is given?
Thank you for seeing that I jumped from 64 to 80. It does need an additional condition added. This is to go up in 8 hour increments and not to exceed 120 hours.
I used tried =IF(G3<1,0,INT(G3)*8+32) it works better than what I have yet it is making the integer round to the next whole number before it needs to.By returning text, I meant that your formula returns text values such as "0", "40" etc. instead of number values 0, 40 etc.
You do want to round G3 down to an integer. To max out at 120:
=IF(G3<1,0,MIN(INT(G3)*8+32,120))
- This work on the minimum stopping at 120.
I am unsure about rounding because it needs to look at date range and see that employee has not been employee the the full year before giving increase in hours.
I am still getting incorrect result when G has not reached 1.00.
