# Nested IF Function not working

Copper Contributor

# 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
3 Replies

# Re: Nested IF Function not working

@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.

# Re: Nested IF Function not working

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)``

# Re: Nested IF Function not working

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.