Forum Discussion
Embry3894
Mar 23, 2022Copper Contributor
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")))))))))))) ...
- Mar 23, 2022
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}))
Embry3894
Mar 23, 2022Copper Contributor
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.
HansVogelaar
Mar 23, 2022MVP
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))
- Embry3894Mar 23, 2022Copper ContributorThis 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.- HansVogelaarMar 23, 2022MVP
Please attach a sample workbook demonstrating the problem (with sensitive info removed) or make it available through OneDrive, Google Drive, Dropbox or similar.
- Embry3894Mar 23, 2022Copper ContributorHow do I attach spreadsheet.