Solved Trying for a multi level if statement in Excel

Tags:
location
IF statement
light
August 29, 2012 at 18:47:08
Specs: Windows XP
My situation is I want to take three types of a location and if at each three it is either a poster or light then it will be this cost:

Locations:
P
P&S
All

At location either:
poster or light

then if it is say P with poster then it is = 28...so each is if:
P - poster - 28
P - light - 20
P&S - poster - 10 +28
P&S - light - 75+20
All - poster - 21
all - ligh - 150

How can I make this an excel if statement for one box based on two drop down lists for location and if poster or light.


See More: Trying for a multi level if statement in Excel

Report •

#1
August 30, 2012 at 06:58:09
✔ Best Answer
Assuming A1 contains your location and A2 contains "poster" or "light"...

=IF(A1="P",IF(A2="poster",28,20),IF(A1="P&S",IF(A2="poster",38,95),IF(A2="poster",21,150)))

Since Excel evaluates formulas from left to right and stops when it find a TRUE condition, we don' have to check for every combination.

For example, once A1="P" is determined to be TRUE, we only have to check for "poster". Since you are using data validation, if the value isn't "poster" then it must be "light" so we don't have to specifically check for "light".

The same hold for "ALL". If A1 isn't "P" or "P&S", then it must be ALL, so the only thing that needs to be checked is A2.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Start a discussion
Related Solutions
See More ↓


Ask Question