Average ignoring Blanks cells

Hi there,

I am trying to Average cells C9:O9 however I need it to ignore cells with #N/A. I am using ISNA in each of the cells C9:O9 to make them blank when no data is Available.
The average sum would be in P9.

I have tried various posts in the forum but none of them help im my case.

If anyone could help that would be great?

Thanks

Justin.

Can you upload example workbook (make sure to remove all private data but try to leva your error cells).

Originally Posted by Justinmih

Hi there,

I am trying to Average cells C9:O9 however I need it to ignore cells with #N/A. I am using ISNA in each of the cells C9:O9 to make them blank when no data is Available.
The average sum would be in P9.

I have tried various posts in the forum but none of them help im my case.

If anyone could help that would be great?

Thanks

Justin.

In Cell P9 type =AVERAGE(c9:o9). It will take care of your blank cells too.

If you want to ignore all errors like #N/A in the range then try this formula

=IF(COUNT(C9:O9),SUMIF(C9:O9,">0")/COUNT(C9:O9),"")

Originally Posted by Justinmih

Hi there,
I doesn't seem average them out for some reason, I have attached the example to let you see.
I have 12 cells to average, if only 2 cells had numbers in it then i would expect it to give me an average of the 2 numbers, the remaining 10 cells would be blank until the information was available.

thanks

Justin.

First Use IFERROR FUNCTION to remove your #N/A. Put =IFERROR(IF(VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE)=0,NA(),VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE))*100,"") from c9 thru o9 then in p9 use =AVERAGE(C9:O9), you will get the desired result.

If Justin is using Excel 2002 as per his profile then IFERROR won't be available to him - and anyway it doesn't make sense to use NA() in the formula to actually generate an error and then use IFERROR to eliminate the error......

This version would work in Excel 2002 to remove any errors or zeroes and give blank cells

=IF(ISERROR(1/VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE)),"",VLOOKUP($E$5,Measures!$A$7:$BC$8,C$1+2,FALSE)*100)

...but isn't it feasible for the absence to be zero?

If you use that version then you'll get errors in row 10 so use this version in row 10 copied across

=IF(C9="","",C9-C8)

Hi Guy's
Thanks to all who have replied.

I see that you mentioned in my profile I am using Excel 2002. I am working between 3 different computers and have noticed that I switching between 2002,2003 and Mac2011, I take it all these versions make a difference?

I have went with the following formulas and they seem to work however I better check on all 3 versions.

In cells C9:O9 they have

Please Login or Register  to view this content.

and in P9

Please Login or Register  to view this content.

I have noticed though it has caused the Graph to report a graph line of zero's where it is pulling the information from C9:O9 is there a way round this?

I have attached the up to date example to let you see.