How to check for 1 of 4 conditions...Help Please

J

jgbadingerjr

I have created a drop list that has four possible selections (Validation)
being A B C or D in the column next to it I want to do a check and a
calculation but I'm having problems with too many "If" scenarios ><. How can
I get this to check the first column for either A, B ,C or D without my
"IF's" conflicting...

Ex:

If cell A2 = A, then A-100 (goes in cell B2)
If cell A2 = B, then B+100 (goes in cell B2)
If cell A2 = C, then C/100 (goes in cell B2)
If cell A2 = D, then D*100 (goes in cell B2)

Thank you
 
D

Dave F

What does this condition mean: If cell A2 = A, then A-100 (goes in cell B2)

A - 100 will give you an error if you try to do that calculation in Excel.
Do you mean A1-100? A2-100? A3-100?

Assuming A2 is what you meant, then
=IF(A2="A",A2-100,IF(A2="B",B2-100,IF(A2="C",C2-100,IF(A2="D",D2*100,""))))

Of course these cell references may be different than what you intend
because your conditions don't give cell references.

Dave
 
J

jgbadingerjr

I apologize, I was trying to keep it basic (generic). Here is what I am
trying to get these series of "IF's" to do...

In cell A2 thru A5 I created a Validation List (NE,SE,SW,NW) these represent
4 possible directions from the Cardinal directions of North, East, South and
West as they are known to be "Quadrants" (Northeast, Southeast, Southwest and
Northwest). The list will keep someone from entering "bad data", what I want
to do next in B2 thru B5 is enter a bearing direction (I have a macro to
convert decimal/degrees but its not necessary at this time) so the format at
this time will be just a whole number (I can change it later because I may
opt not to use the macro). In C2 thru C5 this is where the "function" of IF's
will occur.

Here is what I would like to happen...someone will start in A2 and select a
Quadrant (NE,SE,SW,NW) then in B2 they will enter a number. In C2 the "IF"
scenario will process the previous entered data and one of four things will
happen...If NE then C2 will reflect that number as it is (B2 is B2 nothing
occurs-I guess it could be multiplied by 1 or plus zero) If SE then C2 will
reflect the result of 180-B2, If SW the C2 will reflect the result of 180+B2,
If NW then C2 wil reflect the result of 360-B2. I was also trying to see how
to I think the term is "nest" IF's ...that was the reason for such a generic
explanation, I see now my letters would confuse the cells being called ><.
Thanks for your efforts!
 
D

Dave Peterson

How about:
=IF(A2="","",IF(A2="NE",B2,IF(A2="SE",180-B2,IF(A2="SW",180+B2,360-B2))))

Notice that I didn't check for the last option (NW and 360-B2). Since you have
data|validation in A2, I know that the cell is empty or one of the other 4.

And if it isn't empty and it's not one of the first three, it has to be the last
option (SW).
 
J

jgbadingerjr

Got it! Didn't know you could leave out a list item if the others arent met
then it has to be the only one left^^ Thanks! Big Help!
 
D

Dave Peterson

It all depends on that Data|Validation. Without that, you'd want to do
something else:

=IF(A2="","",IF(A2="NE",B2,IF(A2="SE",180-B2,
IF(A2="SW",180+B2,if(a2="NW",360-B2,"Invalid")))))


Got it! Didn't know you could leave out a list item if the others arent met
then it has to be the only one left^^ Thanks! Big Help!
Click to expand...
 
Top