software

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=quot;12xquot;,((E7*0.7)*0.8)),IF(D7=quot;6xquot;,((E7*0.7)* 0.5))

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

Thanks in advance for any suggestions.
--
Krista



=IF(D7=quot;12xquot;,E7*0.7*0.8,IF(D7=quot;6xquot;,E7*0.7*0.5))

or

=IF(D7=quot;12xquot;,E7*0.56,IF(D7=quot;6xquot;,E7*0.35,0))

which returns 0 if not 12x or 6x

HTH

quot;Kristaquot; wrote:

gt; Hi!
gt;
gt; I'm fairly new to complex calculations and am trying to setup a spreadsheet
gt; with a lot of variables and need some help. Here's one incorrect calc that I
gt; need help with: =IF(D7=quot;12xquot;,((E7*0.7)*0.8)),IF(D7=quot;6xquot;,((E7*0.7)* 0.5))
gt;
gt; I'm trying to do the following in one calcuation:
gt; If D7 = quot;12xquot; then multiple E7 by .7 then that number by .8. But if D7 =
gt; quot;6xquot; then multiple E7 by .7 then that number by .5.
gt;
gt; Thanks in advance for any suggestions.
gt; --
gt; Krista


Try:

=E7*0.7*IF(D7=quot;12xquot;,0.8,IF(D7=quot;6xquot;,0.5,0))--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=538976Thanks much Toppers amp; 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 = quot;Full Pagequot; and Column B = quot;1xquot; 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!
--
Kristaquot;Vitoquot; wrote:

gt;
gt; Try:
gt;
gt; =E7*0.7*IF(D7=quot;12xquot;,0.8,IF(D7=quot;6xquot;,0.5,0))
gt;
gt;
gt; --
gt; Vito
gt; ------------------------------------------------------------------------
gt; Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
gt; View this thread: www.excelforum.com/showthread...hreadid=538976
gt;
gt;

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(A1amp;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(A1amp;B1,L$1:M$24,2,0)),quot;quot;,VLOOKUP(A 1amp;B1,L$1:M$24,2,0))

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

Hope this helps.

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

Thanks Pete!
--
Kristaquot;Pete_UKquot; wrote:

gt; You can only nest up to 7 IF statements, so beyond that you have to
gt; approach things differently. As you surmise, it would be better to have
gt; a table of 24 entries, where the first column is made up of both your
gt; options from column A and B (concatenated together) and then the second
gt; column of your table would be the associated values. Let's assume the
gt; table occupies L1 to M24. Then in C1 you could enter this formula:
gt;
gt; =VLOOKUP(A1amp;B1,L$1:M$24,2,0)
gt;
gt; A1 and B1 are joined together (concatenated) - VLOOKUP tries to find a
gt; match between this first parameter and the first column of the table
gt; defined in the second parameter (in this case L1:M24). If a match is
gt; found, then data is returned from column 2 of the table (the 3rd
gt; parameter). The final parameter indicates what type of match - a zero,
gt; or FALSE, indicates you are looking for an exact match. If there is no
gt; match then the function returns #N/A - if you want to avoid this then
gt; you can change the formula to:
gt;
gt; =IF(ISNA(VLOOKUP(A1amp;B1,L$1:M$24,2,0)),quot;quot;,VLOOKUP(A 1amp;B1,L$1:M$24,2,0))
gt;
gt; and now you would get a blank returned if there is no match.
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
gt;