Solved

Evaluate Null Values in Excel If Statement

Posted on 2014-03-28
2
216 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
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 32

Expert Comment

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

919 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

13 Experts available now in Live!

Get 1:1 Help Now