Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Excel Formula - If And on Blank Cell

Posted on 2016-11-11
Medium Priority
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 27

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 27

Expert Comment

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

Open in new window

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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 27

Accepted Solution

Shaun Kline earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 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