# Excel Formula - If And on Blank Cell

I have an excel database I created to use with Tableau to calculate my Net Promoter Score (NPS) for my CSI results.  The Formula is:

=IF(AND(I2<7,I2=""),1,0)

The result I need is If the cell is less than 7 input a 1, if it is a blank, input a 0.  It is not working, it only enters a 0 for everything.   What am I doing wrong?
###### Who is Participating?

So if the field is blank or equal to/greater than 7, you want 0, else you want 1:
``````IF(OR(I2="", I2>=7), 0, 1)
``````
1

To perform this type of logic, you need to use nested IF statements:
``````=IF(I2<7,1,IF(I2="", 0))
``````

The question becomes: What happens if I2 is equal to or greater than 7?
0

Author Commented:
I tried that as well, it returns a 1 on for the blank cell instead of the 0
0

Switch the formula around:
``````=IF(I2="",0,IF(I2<7, 1))
``````
0

Author Commented:
That works unless the number in the field is greater than 7, than it returns a FALSE.  I tried nesting a third if statement =IF(I52792="",0,IF(I52792>=7,0),IF(I52792<7,1)) and Excel says "You've returned too many arguments for this function".
0

IT ConsultantCommented:

"The result I need is If the cell is less than 7 input a 1, if it is a blank, input a 0"

since you did not specify "what if it is greater than or equal to 7", I assume all other values would be "0"
so then the formula would just be:

``````=IF(I1<7,1,0)
``````

if you want to add another result value, like if the value is not less than 7 and not Blank then the result would be let's say, "2",
you can use the following:

``````=IF(I1<7,1,IF(I1="",0,2))
``````
0

Author Commented:
Thank you so much.  I knew about If And, did not know about If Or.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.