need to reference multiple cells in if statement for calculation

  • Thread starter Thread starter par
  • Start date Start date
P

par

if cell O2 =A and cell P2=EE then true for cell Q2=30, but
if cell 02=B and cell P2=EE then true for cell Q2=60

There are 2 variables for the cells in column O, and 5
variables for column P. Formula is in column Q.
 
to clarify:

Column O can contain either "A" or "B"
Column P can contain "R", "R+1","R+2","D", or "D+1"

Need formula in Column Q that looks at values in O & P and
provides value.

If only one column was needed for variable formula would
look like:

If(P2="R",30,If(P2="R+1",60,If(P2="R+2",115,If(P2="D",60,If(P2="D+1",115,)))))
 
One way is to use a nested "if" statement. For example:

=IF(O2="A",IF(P2="R",99901,IF(P2="R+1",99902,IF(P2="R+2",99903,
IF(P2="D",99904,IF(P2="D+1",99905,"invalid P value"))))),
IF(O2="B",IF(P2="R",99906,IF(P2="R+1",99907,IF(P2="R+2",99908,
IF(P2="D",99909,IF(P2="D+1",99910))))),"invalid O value"))

where the numbers 99901 thru 99910 represent the 10 possible values
resulting from the combination of 2 values in column O and 5 values in column
P. You can replace 99901 thru 99910 with whatever you want.
 
That worked. Thanks. I thought you could only use 7 IFs.
Do you know where there is more information on If
statements? I use them a lot.
 
I once wrote an IF-statement-from-hell that spanned four adjacent cells!
I nested 32 IF statements in one cell and only stopped because I ran
into the 255-character formula length limit, so I made the last value in
the first cell reference the adjacent cell where I nested another 32
IFs, with the last value in the second cell referencing the third cell,
and the last value in the third cell referencing the fourth cell, to end
up with a total of 110 or 120 IFs.
 
OK!

Instead of using a bunch of IF's create a table that lists
the values that correspond to A or B and the values in
column P.

Then the formula to return that value would look something
like this:

=IF(O2="A",VLOOKUP(P2,S2:T6,2,0),IF(O2="B",VLOOKUP
(P2,S7:T11,2,0),""))

Biff
 
Biff's suggestion to use IF with VLOOKUP is a more elegant solution and
yields similar results. There is usually more than one way in Excel to
do just about anything.
 
Back
Top