Solved

Evaluate Null Values in Excel If Statement

Posted on 2014-03-28
2
214 Views
Last Modified: 2014-04-25
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
Comment
Question by:mattturley
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now