Average, but ignore =NA() HELP!!

[Nick24]

I have 3 tables, each has 39 rows and 5 columns.

They are located in the same columns (I-M), above each other. (with a few rows in-between, and titles)

Each cell in the 3 tables contains either a number from 6-100, to two decimal places or an =NA().

I'm trying to make a new table at the bottom, that gets an average cells in the same position of each table. e.g C8, C55, C102.

Sometimes I will have 3 values and I can get a good average, sometimes I have 2 and an =NA(), other times I have 1 Value and 2 =NA()

The problem is, all of these values in the tables are the values for graphs, and for empty values I have had to write '=NA()' so that the graphs don't mess up.

This is the best formula I have found,

=AVERAGE(IF(ISNUMBER(K23),K23),IF(ISNUMBER(K70),K70),IF(ISNUMBER(K117),K117)).

However, this only takes =NA() as zero, and ruins my averages by dropping them down. However when the three values don't have a =NA() it works fine.

Please does someone have a fix for this? I Cant use array formulas as they are not directly next to/above/below each other.

Thanks.

 

[Joe4]

Both solutions offered here seem to work:
http://www.mrexcel.com/forum/excel-questions/685798-average-while-ignoring-zeros-errors.html

 

[Nick24]

This doesn't work as I cant use an array

 

[Jonmo1]

Which version of Excel are you using?

Is there anything common in an adjescent column that indicates which cell needs to be averaged, like a name or something in say column A
So we could do something like
Average column K IF the corresponding value in column A = "fred" (or whatever)
?

See if you can post a sample of your sheet
See my signature for a link to an HTML tool that will help you post a nicely formatted table.

 

[FormR]

Here is one option, it requires array entry:

=AVERAGE(IF(ISNUMBER(CHOOSE({1,2,3},K23,K70,K117)),CHOOSE({1,2,3},K23,K70,K117)))

 

[Aladin Akyurek]

This doesn't work as I cant use an array

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW(K23:K117)-ROW(K23),47)=0,IF(ISNUMBER(K23:K117),K23:K117)))