Mar 30 2024 01:34 AM
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 |
Mar 30 2024 01:51 AM - edited Mar 30 2024 01:53 AM
@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.
Mar 30 2024 01:57 AM
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)
Mar 30 2024 05:11 AM