@average function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chazbri - 16 Jun 2007 01:52 GMT

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

Reply to this Message

Vasant Nanavati - 16 Jun 2007 02:14 GMT

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

array-entered with <Ctrl><Shift><Enter>.

> Hello,
>
[quoted text clipped - 10 lines]
>
> Chazbri

Reply to this Message

Ron Coderre - 16 Jun 2007 02:15 GMT

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

> Hello,
>
[quoted text clipped - 10 lines]
>
> Chazbri

Reply to this Message

T. Valko - 16 Jun 2007 02:30 GMT

Assuming there are no negative numbers in the range:

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

Biff

> Hello,
>
[quoted text clipped - 10 lines]
>
> Chazbri

Reply to this Message

ShaneDevenshire - 16 Jun 2007 05:50 GMT

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.

Signature

Cheers,
Shane Devenshire

> Assuming there are no negative numbers in the range:
>
[quoted text clipped - 16 lines]
> >
> > Chazbri

Reply to this Message

T. Valko - 16 Jun 2007 06:08 GMT

>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,
>
[quoted text clipped - 28 lines]
>> >
>> > Chazbri

Reply to this Message

Roger Govier - 16 Jun 2007 09:33 GMT

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.

Signature

Regards

Roger Govier

> >also Ron's and Nanavati work, but not the other one.
>
[quoted text clipped - 37 lines]
>>> >
>>> > Chazbri

Reply to this Message

ShaneDevenshire - 16 Jun 2007 23:49 GMT

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.

Signature

Cheers,
Shane Devenshire

> Hi Biff
>
[quoted text clipped - 51 lines]
> >>> >
> >>> > Chazbri

Reply to this Message