Average when there are #N/A

[tpheath]

I have two rows (B4:M4 and B6:M6) that will often, by design, contain #N/A. All other cells will contain a numeric value. I would like to calculate the average, but each range below may contain NA.


B4:M4 = outages

B6:M6=unplanned outages


I am trying to get the average number of unplanned outages. Currently using

=SUM(B6:M6)/SUM(B4:M4)


The NA values now throw this off.

 

[oldchippy]

Hi tpheath,


This seems to work, entered with Ctrl+Shift+Enter


=AVERAGE(IF(ISNUMBER(B4:M4),B4:M4))/AVERAGE(IF(ISNUMBER(B6:M6),B6:M6))

 

[Hui]

TPHeath

You could remove the error messages from your existing 2 ranges (B4:M4) & (B6:M6)

by replacing the current formula with =IFERROR(CurrentFormula, 0)

and then use =SUM(B6:M6)/SUM(B4:M4) as you want.

 

[ctop1001]

tpheath,


The formula you have is not an average - it's a ratio of unplanned averages to planned averages. If outages = 0, then that is why you have an error as you cannot divide by zero. If the ratio is what you want, then oldchippy's advice will work well.


I would recommend removing error messages in your existing ranges as Hui suggested - it will make for a cleaner looking spreadsheet.


Hui's suggestion about removing error messages in your existing ranges will only work if you want to average the 0's with the rest of the numbers. If it is a null value, however, and you do not want that cell counted in the average, replace the zero with a blank, represented by quotation marks: "". For example =IFERROR(current formula,"").