Forum Discussion

Copper Contributor

Mar 17, 2022

Solved

How to fix my IF function so it doesn't include blank cells

Good morning, I have created an if/and function formula to assign a score of 1-5 based on what data gets entered in column AG. So far it is calculating correctly but it is also assuming any blank...

HansVogelaar
Mar 17, 2022
megansummer

Why do you have "5", "4" etc.? That makes the scores text values.

Try this:

=IFS($AG2="","",$AG2<=2.5,5,$AG2<=5,4,$AG2<=7.5,3,$AG2<=10,2,$AG2>10,1)

MVP

Mar 17, 2022

Why do you have "5", "4" etc.? That makes the scores text values.

Try this:

=IFS($AG2="","",$AG2<=2.5,5,$AG2<=5,4,$AG2<=7.5,3,$AG2<=10,2,$AG2>10,1)

megansummer
Copper Contributor
Mar 17, 2022
Hi Hans, I see what you mean about the "". I'm face-palming myself for that one.

However the formula you suggested returns a #NAME? error, and if I adjust my original formula to remove the "" I still have the same issue with the blanks =IF(AND($AG2<=2.5,$AG2>=0),5,IF(AND($AG2>2.5,$AG2<=5),4,IF(AND($AG2>=5.01,$AG2<=7.5),3,IF(AND($AG2>=7.51,$AG2<=10),2,IF($AG2>=10.01,1)))))

UPDATE: I did some additional research and it seems my company uses a version of Excel 2016 that does not offer the IFS function, so unfortunately I need to stick to the longer if/and formulas, but I'm still not sure how to resolve the blank issue. Thanks!
- megansummer
  Copper Contributor
  Mar 17, 2022
  I figured it out! =IF($AG2=ISBLANK(""),"",IF(AND($AG2<=2.5,$AG2>=0),5,IF(AND($AG2>2.5,$AG2<=5),4,IF(AND($AG2>=5.01,$AG2<=7.5),3,IF(AND($AG2>=7.51,$AG2<=10),2,IF($AG2>=10.01,1))))))
  
  Thanks again!
  - HansVogelaar
    MVP
    Mar 17, 2022
    megansummer
    
    Glad you figured it out! I'd shorten it to
    
    =IF($AG2="","",IF($AG2<=2.5,5,IF($AG2<=5,4,IF($AG2<=7.5,3,IF($AG2<=10,2,1)))))

Resources