PDA

View Full Version : Calculating Averages



Mattster2020
01-19-2009, 04:07 AM
Morning All,

I am using the below formula to calculate an average:

=AVERAGE(Data!H2:H1000)

The problem I have is that the data range contains zero's which effect the average, is there anyway to exclude zero's and only count an average for anything greater than zero?

Regards,

Matt

Simon Lloyd
01-19-2009, 04:23 AM
Perhaps use: =SUM(Data!H2:H1000)/COUNTIF(Data!H2:H1000,">0")

Morning All,

I am using the below formula to calculate an average:

=AVERAGE(Data!H2:H1000)

The problem I have is that the data range contains zero's which effect the average, is there anyway to exclude zero's and only count an average for anything greater than zero?

Regards,

Matt

Bob Phillips
01-19-2009, 04:35 AM
Try this array formula

=AVERAGE(IF(Data!H2:H1000<>0,Data!H2:H1000))

Simon Lloyd
01-19-2009, 04:47 AM
Bob could the DAVERAGE function be used or would that ignore negative values?

Bob Phillips
01-19-2009, 04:56 AM
No, DAVERAGE works fine. I bet the OP wants >0 values though, no negatives.

mdmackillop
01-19-2009, 06:49 AM
=SUM(Data!H2:H1000)/COUNTIF(Data!H2:H1000,">0")

Bob Phillips
01-19-2009, 07:07 AM
Hmm, hmm! Simon said the same thing in post #2.

mdmackillop
01-19-2009, 08:27 AM
:blush I must read posts more carefully!

Zack Barresse
01-19-2009, 08:29 AM
This may be a silly question, but if you want to ignore the zero values, maybe you should examine why they're there in the first place. Was it just a placeholder? To fill a spot? I would suggest you look further than just a simple workaround formula. If the values which are returned zero for you, aren't actually worth a value of zero, you can change it to, say, text, you could use your AVERAGE() formula. Just a thought.

Simon Lloyd
01-19-2009, 08:37 AM
:blush I must read posts more carefully!Late night or empty bottle? ;)

Bob Phillips
01-19-2009, 10:26 AM
:blush I must read posts more carefully!

It was oddly placed, the answer then the quote (he's a Manchester lad Malcom, says it all really).

Simon Lloyd
01-19-2009, 01:27 PM
Hey answer in the quote!
I'm a little offended at....
It was oddly placed, the answer then the quote (he's a Manchester lad Malcom, says it all really).I'm Welsh, and Malcolm will definitely side with another Celt, we'll see you in April!!!

Bob Phillips
01-19-2009, 04:08 PM
Obvious really, Lloyd is a Welsh name. But tough t...., my name is Phillips, I am also Welsh stock, so I am a celt too!

Aussiebear
01-19-2009, 07:36 PM
A gathering of the clans perhaps?

Simon Lloyd
01-20-2009, 02:08 AM
Obvious really, Lloyd is a Welsh name. But tough t...., my name is Phillips, I am also Welsh stock, so I am a celt too!They'll let anybody in these days!!!!!, what is the world coming too?

Zack Barresse
01-20-2009, 10:03 AM
Welsh? I thought you were a Wessex Man... Turns out you're just like me, a mutt. :cool:

Bob Phillips
01-20-2009, 10:46 AM
I am a Wessex man, but from Welsh stock. My granddad was born in Wales, but went east as a young man.

Zack Barresse
01-20-2009, 11:35 AM
And a flag flying of Chile colors...

Bob Phillips
01-20-2009, 01:15 PM
I'm eclectic ...

Zack Barresse
01-20-2009, 01:47 PM
On first glance one might think senile... :devil2:

Aussiebear
01-20-2009, 01:53 PM
Who's on topic here???

Zack Barresse
01-20-2009, 02:09 PM
Err, hrmm, I guess the OP only had the one post, in 3 pages... :whistle:

Simon Lloyd
01-20-2009, 02:10 PM
I am a Wessex man, but from Welsh stock. My granddad was born in Wales, but went east as a young man.Young and foolish!, East? who ever heard of a man going East?

i'm eclectic more like eccentric my good fellow! ;)

Aussie, we were all on topic but the op decided not to grace us with his feedback or presence so we are taking this rare moment to abuse some open ground!