Forum Discussion
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 values are 0's and assigning a "5". How do I adjust my formula so that it does not calculate anything if the cell is blank? Any help is appreciated!
Here is my current formula:
=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")))))
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)
4 Replies
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)
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!
- 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!
