1. ## Calculating Averages

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

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

3. Try this array formula

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

4. Bob could the DAVERAGE function be used or would that ignore negative values?

5. No, DAVERAGE works fine. I bet the OP wants >0 values though, no negatives.

6. =SUM(Data!H2:H1000)/COUNTIF(Data!H2:H1000,">0")

7. Hmm, hmm! Simon said the same thing in post #2.

8. I must read posts more carefully!

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

10. Originally Posted by mdmackillop
I must read posts more carefully!
Late night or empty bottle?

11. 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).

12. 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!!!

13. 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!

14. A gathering of the clans perhaps?

15. 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?

16. Welsh? I thought you were a Wessex Man... Turns out you're just like me, a mutt.

17. I am a Wessex man, but from Welsh stock. My granddad was born in Wales, but went east as a young man.

18. And a flag flying of Chile colors...

19. I'm eclectic ...

20. On first glance one might think senile...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•