Multiple Conditions
 

Hi!

I'm fairly new to complex calculations and am trying to setup a spreadsheet
with a lot of variables and need some help. Here's one incorrect calc that I
need help with: =IF(D7="12x",((E7*0.7)*0.8)),IF(D7="6x",((E7*0.7)* 0.5))

I'm trying to do the following in one calcuation:
If D7 = "12x" then multiple E7 by .7 then that number by .8. But if D7 =
"6x" then multiple E7 by .7 then that number by .5.

Thanks in advance for any suggestions.
--
Krista

Multiple Conditions
 

=IF(D7="12x",E7*0.7*0.8,IF(D7="6x",E7*0.7*0.5))

or

=IF(D7="12x",E7*0.56,IF(D7="6x",E7*0.35,0))

which returns 0 if not 12x or 6x

HTH

"Krista" wrote:

Hi!

I'm fairly new to complex calculations and am trying to setup a spreadsheet
with a lot of variables and need some help. Here's one incorrect calc that I
need help with: =IF(D7="12x",((E7*0.7)*0.8)),IF(D7="6x",((E7*0.7)* 0.5))

I'm trying to do the following in one calcuation:
If D7 = "12x" then multiple E7 by .7 then that number by .8. But if D7 =
"6x" then multiple E7 by .7 then that number by .5.

Thanks in advance for any suggestions.
--
Krista

Multiple Conditions
 

Try:

=E7*0.7*IF(D7="12x",0.8,IF(D7="6x",0.5,0))


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=538976

Multiple Conditions
 

Thanks much Toppers & Vito for the answers to my initial question!

Now I've got another scenario with multiple variables

Column A: I could have one of 6 options from a drop-down list
Column B: I could have one of 4 options from a drop-down list
Column C: I need to put in some kind of conditional statment that says if
Column A = "Full Page" and Column B = "1x" then input $10,000 etc

As you can see there are a total of 24 variables each with a different input
amount for Column C.

Can anyone let me know what the best way to do this would be? Is it
possible to set up a table on another worksheet then reference that table in
Column C???

Thanks!
--
Krista


"Vito" wrote:


Try:

=E7*0.7*IF(D7="12x",0.8,IF(D7="6x",0.5,0))


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=538976

Multiple Conditions
 

You can only nest up to 7 IF statements, so beyond that you have to
approach things differently. As you surmise, it would be better to have
a table of 24 entries, where the first column is made up of both your
options from column A and B (concatenated together) and then the second
column of your table would be the associated values. Let's assume the
table occupies L1 to M24. Then in C1 you could enter this formula:

=VLOOKUP(A1&B1,L$1:M$24,2,0)

A1 and B1 are joined together (concatenated) - VLOOKUP tries to find a
match between this first parameter and the first column of the table
defined in the second parameter (in this case L1:M24). If a match is
found, then data is returned from column 2 of the table (the 3rd
parameter). The final parameter indicates what type of match - a zero,
or FALSE, indicates you are looking for an exact match. If there is no
match then the function returns #N/A - if you want to avoid this then
you can change the formula to:

=IF(ISNA(VLOOKUP(A1&B1,L$1:M$24,2,0)),"",VLOOKUP(A 1&B1,L$1:M$24,2,0))

and now you would get a blank returned if there is no match.

Hope this helps.

Pete

Multiple Conditions
 

I'm just now getting around to trying this again and yes, that's a huge help.

Thanks Pete!
--
Krista


"Pete_UK" wrote:

You can only nest up to 7 IF statements, so beyond that you have to
approach things differently. As you surmise, it would be better to have
a table of 24 entries, where the first column is made up of both your
options from column A and B (concatenated together) and then the second
column of your table would be the associated values. Let's assume the
table occupies L1 to M24. Then in C1 you could enter this formula:

=VLOOKUP(A1&B1,L$1:M$24,2,0)

A1 and B1 are joined together (concatenated) - VLOOKUP tries to find a
match between this first parameter and the first column of the table
defined in the second parameter (in this case L1:M24). If a match is
found, then data is returned from column 2 of the table (the 3rd
parameter). The final parameter indicates what type of match - a zero,
or FALSE, indicates you are looking for an exact match. If there is no
match then the function returns #N/A - if you want to avoid this then
you can change the formula to:

=IF(ISNA(VLOOKUP(A1&B1,L$1:M$24,2,0)),"",VLOOKUP(A 1&B1,L$1:M$24,2,0))

and now you would get a blank returned if there is no match.

Hope this helps.

Pete