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")

Originally Posted by Mattster2020

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.

**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

Originally Posted by mdmackillop

I must read posts more carefully!

Late night or empty bottle?

**Bob Phillips** · 01-19-2009, 10:26 AM

Originally Posted by mdmackillop

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!

Originally Posted by Simon Lloyd

I'm a little offended at....

Originally Posted by xld

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

Originally Posted by xld

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.

**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...

Thread: Calculating Averages

Thread Tools

Display

Calculating Averages

Posting Permissions