need to reference multiple cells in if statement for calculation

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.
 
P

PAR

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,)))))
 
G

Guest

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.
 
B

Biff

Hi!

If column O contains "B" are there a different set set of
values to return?

Biff
 
P

PAR

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.
 
G

garfield-n-odie

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.
 
B

Biff

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
 
G

garfield-n-odie

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filling formulas with specific cell references remaining the same 1
Conditional format refers to two cells 1
Showing the cell to the Left when certain criteria are met 2
Need a function to fill column from other columns 5
Variable calculation question 1
Macro to copy data when data is detected in another column(s). 2
Macro: Updates a formula or value when a data is entered. 4
To combine various cell formulae in to the final cell 1

Top