Using Average IF Statement in Excel

[Syndrome78]

I know you can do SUMIF or COUNTIF, but I would like to do an AVERAGEIF. I have a spreadsheet where I only want to average a group of percentages from column "M", but only if the amount in column "L" is greater than 0.

 

[ajrimmer]

How about doing sumif(...) / countif(...) ?

 

[Syndrome78]

It doesn't work. The COUNTIF statement errors out everytime. It says there are too many arguments for this function.

 

[AnotherHiggins]

what are these percentages? I ask because one should NEVER "average averages".

Consider the following example:

A) 10 out of 1000 = 1%

B) 20 out of 2000 = 1%

C) 9 out of 10 = 90%


The "average of the averages" is 31%

The sum of 10, 20 and 9 is 39. The sum of 1000, 2000 and 10 is 3010. Dividing 39 by 3010 gives you 1.3%.

1% vs 31 % is a big, big difference. I have actually run across people in the workplace who don't seem to think this matters. They argue that there won't be any differences that extreme in their business case. My reply is, why would you want to see numbers that you know are wrong?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Syndrome78]

The percentages are Load Factors for companies. Each class has an average Load Factor and within that class each company has their own Load Factor. I would like to take each Load Factor whose Savings is greater than zero and average them.

 

[SkipVought]

How about posting your formulas.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Syndrome78]

The Load Factor formula is =SUM(range values)/(Max(range values)*(24*365)). The Savings formula is =(B2-C2)/B2. Some companies have negative Savings.

 

[SkipVought]

"It doesn't work. The COUNTIF statement errors out everytime. It says there are too many arguments for this function."

I don't see any COUNTIF

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TonyJollans]

It doesn't work. The COUNTIF statement errors out everytime. It says there are too many arguments for this function.

My guess is you are copying the SUMIF and changing the name to COUNTIF; you must also delete the third parameter. SUMIF needs two ranges (one for the criteria and one to sum) whereas COUNTIF doesn't need a second range to count.

[tt][blue]=SUMIF(L2:L50,">0",M2:M50)/COUNTIF(L2:L50,">0")[/blue][/tt]

.. ought to do it.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Syndrome78]

You were correct and thank you for your help. I was using the formula =SUMIF(L2:L67,">0",M2:M67)/COUNTIF(L2:L67,">0",M2:M67). It worked perfectly when I deleted the third parameter.

Oh and Skip, I misunderstood which formulas you wanted to see.

Thank All of you for your assistance.

 

[remeng]

=if(sum(L)>0,average(M),"L is less than or equal to 0")