Combining IF Statements

You "nest" the if statements, like this example from one of my sheets:


In the Excel help system, go to the index and look up the IF worksheet
function. Exerpt:

Remarks
Up to seven IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. See the following last example.

Suppose you want to assign letter grades to numbers referenced by the name
AverageScore. See the following table.

If AverageScore is Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F

You can use the following nested IF function:

IF(AverageScore89,"A",IF(AverageScore79,"B",
IF(AverageScore69,"C",IF(AverageScore59,"D","F") )))
In the preceding example, the second IF statement is also the value_if_false
argument to the first IF statement. Similarly, the third IF statement is the
value_if_false argument to the second IF statement. For example, if the
first logical_test (Average89) is TRUE, "A" is returned. If the first
logical_test is FALSE, the second IF statement is evaluated, and so on.

Finally, do a google search for "nesting if statements in Excel" (without
the quotes). I came up with enough reading to stay busy for a week.

=SUMPRODUCT((L5:L200={"Rich Burn","Lean
Burn"})*(N5:N200))/SUMPRODUCT(--(L5:L200={"Rich Burn","Lean Burn"}))

--

Regards,

Peo Sjoblom


"sowetoddid" wrote in message
...

Does anyone know how to do this?


--
sowetoddid
------------------------------------------------------------------------
sowetoddid's Profile:
http://www.excelforum.com/member.php...fo&userid=2096
View this thread: http://www.excelforum.com/showthread...hreadid=161425

Sorry, didn't see the second column criteria with the horsepower, try this
instead

=SUMPRODUCT((L5:L200={"Rich Burn","Lean
Burn"})*(M5:M200=600)*(N5:N200))/SUMPRODUCT((L5:L200={"Rich Burn","Lean
Burn"})*(M5:M200=600))

--

Regards,

Peo Sjoblom


"Peo Sjoblom" wrote in message
...
=SUMPRODUCT((L5:L200={"Rich Burn","Lean
Burn"})*(N5:N200))/SUMPRODUCT(--(L5:L200={"Rich Burn","Lean Burn"}))

--

Regards,

Peo Sjoblom


"sowetoddid" wrote in message
...

Does anyone know how to do this?


--
sowetoddid
------------------------------------------------------------------------
sowetoddid's Profile:
http://www.excelforum.com/member.php...fo&userid=2096
View this thread:
http://www.excelforum.com/showthread...hreadid=161425

Wow, Peo Sjoblom that is an amazing way of doing it.

Just because I am curious....could you explain the logic behind that.

Many thanks.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Yes!

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"sowetoddid" skrev i en meddelelse
...
Is it possible to combine an IF statement with a SUMIF/COUNTIF??



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Will someone show me how based on the posts above??



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Why don't you disclose, what you are trying to
accomplish? It will be much easier to advise.

Example:

=IF(SUMIF(A1:A10, ""&C4,B1:B10)2000,"Too much","OK")


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"sowetoddid" skrev i en meddelelse
...
Will someone show me how based on the posts above??



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Please don't attach files to postings.
BTW none were attached, so it was probably
removed by ExcelTip.Com

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"sowetoddid" skrev i en meddelelse
...
I am attaching a sample file of my data. Only columns "L" through "S"
are included.

File Attached: http://www.excelforum.com/attachment.php?postid=341342
(sample.xls)

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

If I have understood you properly, this setup will do
the job:

Assuming your data in Sheet3!L2:P100

In Sheet4:

To get the average of PM for 600 hp

=AVERAGE(IF((Sheet3!L2:L100="Rich
Burn")*(Sheet3!M2:M100=600),Sheet3!N2:N100))


To get the average of VOC for 600 hp

=AVERAGE(IF((Sheet3!L2:L100="Rich
Burn")*(Sheet3!M2:M100=600),Sheet3!O2:O100))


To get the average of NOx for 600 hp

=AVERAGE(IF((Sheet3!L2:L100="Rich
Burn")*(Sheet3!M2:M100=600),Sheet3!P2:P100))


The formulas are array formulas and must be entered with
ShiftCtrlEnter
instead of Enter, also if edited later. If done correctly, Excel will
display the formula
in the formula bar enclosed in curly brackets { } Don't enter these brackets
yourself.

You can use cell references instead of actual text or number. E.g. if you
have the text
"Rich Burn" (without quotes!) in Sheet4!F1 and 600 in Sheet4!G1, the formula
for PM would be:

=AVERAGE(IF((Sheet3!L2:L100=F1)*(Sheet3!M2:M100=G1 ),Sheet3!N2:N100))

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"sowetoddid" skrev i en meddelelse
...
I do not know the actual function syntax to complete these averages.

The logic behind it is....

What is the average PM (particulate matter) emission from a 600hp, rich
burn engine?

If column "L" says "Rich Burn", then move to the same row of column "M"
and check the horsepower rating. If this cell has the right number
(ex. 600), then select the corresponding PM value from column "N".

Continue this process through the spreadsheet and finally average the
selected PM values from column "N"



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/