Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Evaluate Null Values in Excel If Statement

Posted on 2014-03-28
2
Medium Priority
?
247 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 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 34

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

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.

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.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

783 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