Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Nested IF statement to compare values in 3 columns

Posted on 2011-10-09
6
Medium Priority
?
208 Views
Last Modified: 2012-05-12
Hi Experts

Would like to implement using IF statement or VBA

If there is value is zero for 2009, 2010,2011 then not used in "3yrs"
if any of them is true would like  years' in which used .
IF used in all three years then "High Usage"

or suggestion to bett
data.xls
0
Comment
Question by:macentrap
6 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 36940022
Hi macentrap.

Please see attached. Formula is...
=IF(SUM(C2:E2)=0,"No Usage 3yrs",IF(AND(C2<>0,D2<>0,E2<>0),"High Usage",SUBSTITUTE("USED "&IF(C2<>0,$C$1&", ","")&IF(D2<>0,$D$1&", ","")&IF(E2<>0,$E$1&", ",""),", ","",COUNTIF(C2:E2,"<>0"))))

Open in new window

Regards,
Brian.
data-V2.xls
0
 
LVL 5

Expert Comment

by:neothwin
ID: 36940037
here is the formula for row two.
=IF(SUM(C2:E2)>0,IF(C2*D2*E2>0,"High Usage","USED "&IF(C2>0,$C$1,"")& " " &IF(D2>0,$D$1,"")& " " &IF(E2>0,$E$1,"")),"No Usage 3yrs")
0
 
LVL 7

Author Closing Comment

by:macentrap
ID: 36940045
Thank You
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 36940057
neothwin - the extra code in my solution was to provide commas and avoid extra spaces!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 36940062
Thanks, macentrap!
0
 
LVL 50
ID: 36940068
I realise this question is closed, but for the record:

Both

IF(AND(C2<>0,D2<>0,E2<>0)

and

IF(C2*D2*E2>0

can also be expressed with

IF(Countif(C2:E2,">0")

The benefit is speed. It's only one operation as opposed to three comparisons or two multiplications.

cheers, teylyn
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

810 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