Forum Discussion
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"))))))))))))
G has a formula to calculate years of service.
This function works until it gets to 3rd condition and starts working with the final condition.
It also does not work when the formulas in G is looking at F and not E to calculate the Year of service.
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}))
12 Replies
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}))
- This works!!
Thank you for the help
- Try this instead:
=VLOOKUP(G98,{0,0;1,40;2,48;3,56;4,64;5,80;6,88;7,96;8,104;9,112;10,120},2,1)- This is not providing me with correct numbers.
G value of 0.02- 0.28 is giving result of 40 and it needs to be 0.
G Value of 0.29 - 0.86 is giving result of 48 and it needs to be 0.
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))
