Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Evaluate Null Values in Excel If Statement

Posted on 2014-03-28
Medium Priority
236 Views
I'm using an Excel spreadsheet with multiple pick from list cells to calculate a text value (based on a weighted numbering in adjacent cells).  I need to evaluate C2:C11 such that if any cell is null, C12 reads "Select a Value for Each Concept."  Once all cells have been filled, I then need to display the text I am currently displaying with the following statement.

=IF(D12<=30, "Every Five Years", IF(D12<=60, "Every Three Years", IF(D12>60, "Every Year")))

Any thoughts on accomplishing?
RevisionMatrix.xlsx
0
Question by:mattturley
[X]
###### 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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39962638
Try this revised version

=IF(COUNTBLANK(C2:C11),"Select a Value for Each Concept.",IF(D12<=30, "Every Five Years", IF(D12<=60, "Every Three Years", IF(D12>60, "Every Year"))))

You will only get your original values once all of C2:C11 is populated

regards, barry
0

LVL 33

Expert Comment

ID: 39966178
Alternatively, set up a small lookup table:

0      Every 5 Years
31      Every 3 Years
61      Every Year

Then have a lookup formula for the second half of the formula:

=IF(COUNTBLANK(C2:C11),"Select a Value for Each Concept.",VLOOKUP(D12,A1:B4,2)

Where table is in range A1:B3

This way if the ranges change, you only have to change the values in the left hand column of the table rather than in the formula.

Thanks
Rob H
0

## Featured Post

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month5 days, 2 hours left to enroll