Combining IF Statements

  • Thread starter Thread starter sowetoddid
  • Start date Start date
S

sowetoddid

Pertaining to my previous post...


Before to calculate an average based on the contents of one column and
the corresponding cell in another column, I used SUMIF and COUNTIF.

How can I add another IF statement to this function?

Column "L" contains "Rich Burn" or "Lean Burn"
Column "M" contains "600", "550", etc. (horsepower ratings)
Column "N" contains the number that need to be averaged.

I would like to average only the values in "N" that are associated with
"Rich Burn" and a horsepower of "600".



Thanks.
 
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(AverageScore>89,"A",IF(AverageScore>79,"B",
IF(AverageScore>69,"C",IF(AverageScore>59,"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 (Average>89) 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"}))
 
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 said:
=SUMPRODUCT((L5:L200={"Rich Burn","Lean
Burn"})*(N5:N200))/SUMPRODUCT(--(L5:L200={"Rich Burn","Lean Burn"}))
Click to expand...
 
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.
 
Let me clarify a little more what I am trying to do...

I want a formula that will average only certain numbers. The AVERAGE
function has not worked. The only combination that has somewhat
resembled the right one is...

=SUMIF(L1:L1000,"Rich-Burn",N1:N1000)/COUNTIF(L1:L1000,"Rich-Burn")

Does anyone know how to incorporate an IF statement (or any other
function) into this such that column "M" will also restrict the data
being averaged.


The SumProduct command worked, except that it averages "Rich Burn"
values based on a total number of values that includes "Lean Burn".
"Rich Burn" values should be averaged amongst themselves and not
influenced by the number of "Lean Burn" values.
 
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.
 
Depending on the type of engine (rich burn or lean burn) and the
horsepower rating (600, 550, etc), emissions (PM, VOC, NOx, SO2, CO,
H2S) will vary.

I would like to average...

PM values (column N) for a 600 hp (column M), rich burn engine(column
L)

VOC values (column O) for a 550 hp (column M), rich burn engine (column
L)

VOC values (column O) for a 550 hp (column M), lean burn engine (column
L)

etc.

All of these values, which appear on Sheet 3, will be averaged on sheet
4 in a separate table for Rich Burn and Lean Burn Emissions.


Column "L" contains "Rich Burn" or "Lean Burn"
Column "M" contains "600", "550", etc. (horsepower ratings)
Column "N" contains the PM numbers that need to be averaged.
Column "O" contains the VOC numbers that need to be averaged.
Column "P" contains the NOx numbers that need to be averaged.
etc.

Does that help?

Sorry for the confusion
 
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"
 
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.
 
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
<Shift><Ctrl><Enter>
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.
 
Back
Top