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}))
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.
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.Please attach a sample workbook demonstrating the problem (with sensitive info removed) or make it available through OneDrive, Google Drive, Dropbox or similar.
- How do I attach spreadsheet.