Excel Formula - If And on Blank Cell

Posted on 2016-11-11
Last Modified: 2016-11-15
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:


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?
Question by:mikesautobody
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 26

Expert Comment

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

Open in new window

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

Author Comment

ID: 41884131
I tried that as well, it returns a 1 on for the blank cell instead of the 0
LVL 26

Expert Comment

by:Shaun Kline
ID: 41884136
Switch the formula around:
=IF(I2="",0,IF(I2<7, 1))

Open in new window

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 41884152
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".
LVL 26

Accepted Solution

Shaun Kline earned 500 total points
ID: 41884156
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)

Open in new window


Expert Comment

ID: 41885999
Going back to your requirement:  

"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:


Open in new window

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:


Open in new window


Author Closing Comment

ID: 41888469
Thank you so much.  I knew about If And, did not know about If Or.

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question