Link to home
Start Free TrialLog in
Avatar of macentrap
macentrapFlag for Australia

asked on

Nested IF statement to compare values in 3 columns

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
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of neothwin
neothwin

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")
Avatar of macentrap

ASKER

Thank You
neothwin - the extra code in my solution was to provide commas and avoid extra spaces!
Thanks, macentrap!
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