Forum Discussion
Nested IF Function not working
Hi I have a column of temperature and the percentage.
I want the percentage column (D) is based on the temperature column (C) where:
IF 30=7
31=7
32=9
33=9
34=11
35=11
Else =0
I have try to do using nested IF, but none works and gives all the value =0.
=IF(C2="30",7,IF(C2="31",7, IF(C2="32",9,IF(C2="33",9,IF(C2="34",11,IF(C2="35",11,0))))))
I also attached the a picture for references.
Thank you.
| Date_Time | Day | Temperature | Percentage (%) |
| 01-08-20 0:00 | Saturday | 27 | 0 |
| 01-08-20 1:00 | Saturday | 26 | 0 |
| 01-08-20 2:00 | Saturday | 26 | 0 |
| 01-08-20 3:00 | Saturday | 26 | 0 |
| 01-08-20 4:00 | Saturday | 26 | 0 |
| 01-08-20 5:00 | Saturday | 26 | 0 |
| 01-08-20 6:00 | Saturday | 26 | 0 |
| 01-08-20 7:00 | Saturday | 26 | 0 |
| 01-08-20 8:00 | Saturday | 26 | 0 |
| 01-08-20 9:00 | Saturday | 28 | 0 |
| 01-08-20 10:00 | Saturday | 29 | 0 |
| 01-08-20 11:00 | Saturday | 30 | 0 |
| 01-08-20 12:00 | Saturday | 31 | 0 |
| 01-08-20 13:00 | Saturday | 32 | 0 |
| 01-08-20 14:00 | Saturday | 32 | 0 |
| 01-08-20 15:00 | Saturday | 33 | 0 |
| 01-08-20 16:00 | Saturday | 34 | 0 |
| 01-08-20 17:00 | Saturday | 32 | 0 |
| 01-08-20 18:00 | Saturday | 31 | 0 |
| 01-08-20 19:00 | Saturday | 30 | 0 |
| 01-08-20 20:00 | Saturday | 30 | 0 |
| 01-08-20 21:00 | Saturday | 29 | 0 |
| 01-08-20 22:00 | Saturday | 29 | 0 |
| 01-08-20 23:00 | Saturday | 29 | 0 |
Neruu See if the attached workbook does what you need. I prefer to use a lookup table over hard-coding limits in a formula with nested IF functions.
Make sure that the temperatures are numbers, since when I copied the data into a workbook, I got texts with a trailing space. Something like "30 ".
With your original formula, you were looking for "30" which is not equal to "30 ". Hence, it always returned zero.
I prefer to avoid nesting more than 3 IFs and I don't use IFS.
Two approaches:
Option 1: Create a dedicated lookup table and use XLOOKUP:
=XLOOKUP(C2:C25,temp,percentage,0,-1)Option 2: Use SWITCH to simplify the logic:
=SWITCH(C2:C25,30,7,31,7,32,9,33,9,34,11,35,11,0)- Thank you Riny_van_Eekelen and Patrick2788 for the feedback.
Much appreciated for the solution given. I have managed to use the LOOKUP and SWITCH function on different date and time.
