@average function

  • Thread starter Thread starter Chazbri
  • Start date Start date
C

Chazbri

Hello,

I have a worksheet that has the months of the year in one column and a
dollar amount in the next column. At the bottom I have summed the dollar
amount column.

My question:

What would the formula be if I wanted to average the dollars over the months
that have a dollar amount greater than zero?

Thank you very much for your help.

Chazbri
 
=SUM((B1:B12>0)*(B1:B12))/SUM(--(B1:B12>0))

array-entered with <Ctrl><Shift><Enter>.
 
With
B1:B10 containing numbers with some zeros and/or blanks

This ARRAY FORMULA returns the average of the values greater than zero:
C1: =AVERAGE(IF(B1:B10>0,B1:B10))

Note: For array formulas, [Ctrl] [Shift] and press [Enter], instead of just
pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Assuming there are no negative numbers in the range:

=SUM(B1:B20)/COUNTIF(B1:B20,">0")

Biff
 
Hi,

A few corrections and additional suggestions:

You could use any of the following:


=AVERAGEIF(B1:B12,">0") in 2007
=SUMIF(B1:B12,">0")/COUNTIF(B1:B12,">0") no array

also Ron's and Nanavati work, but not the other one.
 
also Ron's and Nanavati work, but not the other one.

I guess you're referring to mine as the other one? What about it doesn't
work?

Biff
 
Hi Biff

You did preface your formula, with
Assuming there are no negative numbers in the range
in which case it would be absolutely fine

I think Shane was referring to the fact that the OP said
average the dollars over the months that have a dollar amount greater
than zero?

I think that all other posters have implied from this that there may be
negative numbers, but I agree that may not be what the OP was saying.
I guess using SUMIF(B1:B20,">0") covers both scenarios.
 
Yes Roger,

This is really a question of how we read the question and I assumed that >0
did not imply that all numbers were positive in the original data set. And I
did notice the preface but I assumed that a different question was being
asked. As we all know we are up against the wall to guess what questions
often mean. Hopefully users can work out what it is they are asking and
therefore which solutions meet their needs. Nothing wrong with SUM(...)/ if
there are no negative values.
 
Back
Top