using the Average calculation with cells that contain 0 or no data at all

[mikester]

Hi,


First post so please be gentle! I'm working on my MPG spreadsheet and my current averaging method is not providing the true average for some reason so i'm having to teach myself the fundamentals of formulas and COUNT IF, ">0" etc

However i'm still struggling...


i have cells that have the value of '0' and are killing my average, also the cells that have 0 are being counted as units, i'e i've got 5 cells, only 3 have data in so far so it'd be the total of these 3 divided by 3, but at the moment it will be divided by 5.


i'm working over several sheets so i'm hoping it's something in the way i've typed the formula rather than the formula itself.


Please be gentle if it's very wrong!!

=AVERAGE('Mar-Oct'!J6:J20, ">0", 'Oct-May'!J5:J20, ">0", 'May-'!J5:J20, ">0", '2011'!J6:J21, ">0")


i'm getting this " #VALUE! "


can someone not only tell me the correct layout but explain how it works please?



Thanks in advance!!

 

[barry houdini]

Hello mikester, welcome to MrExcel

I notice that the ranges are different sizes on different sheets, is that deliberate? Which version of Excel are you using?

It's probably difficult to use AVERAGE function here because you want to exclude zeroes. Perhaps sum everything and then divde by the total number of values >0, i.e.

=SUM('Mar-Oct'!J6:J20,'Oct-May'!J5:J20,'May-'!J5:J20,'2011'!J6:J21)/(COUNTIF('Mar-Oct'!J6:J20,">0")+COUNTIF('Oct-May'!J5:J20,">0")+COUNTIF('May-'!J5:J20,">0")+COUNTIF('2011'!J6:J21,">0"))