Average with missing data

[ReeGiGi]

Hello
I am one f those people that can normally solve an excel problem with a search. Unfortunately, I can't find what I am looking for this time. Please help )

Here is my problem, I need to be able to create an average total that skips any missing information. For example, if I have four values I need the average for all four (e.g. 5+6+7+3/4), but if one is missing I need the average of three (e.g. 5+7+3/3).

I found the array formula: =AVERAGE(IF($A$1:$A$5<>0,$A$1:$A$5)) [Note: I pressed Ctrl+Shift+Enter to make it an array formula]. Which works perfectly for cells that are next to each other (e.g. A4:W4). The trouble I am having though is that the cells I need to pull the data from are not next to each other e.g. (K4, N4, S4, W4).

I did try to ammend the array formula with the following: =AVERAGE(IF($K$4,$N$4,$S$4,$W$4<>0,$K$4,$N$4,$S$4,$W$4)). But of course that came back with a "too many arguments" error.

How can I ammend this information to make this formula work.

Extra information if needed: The cells that have missing data are not blank or have a 0 value, they show a "#N/A" (which doesn't need fixing).

 

[Haseeb Avarakkan]

Hello

Here is one way:

Firstly used a MATCH formula with column & look for column K, N, S & W. Which is 11, 14, 19 & 23 respectively. Then divided the range, If zeros, errors or texts are in the range will give error, using ISNUMBER will pick up only TRUE conditions.

Array Formula:

=AVERAGE(IF(ISNUMBER(MATCH(COLUMN(K4:W4),{11,14,19,23},0)/(K4:W4)),K4:W4))

 

[vogel997]

Try this

19
20
9
2
15
20
14.17	Average
=AVERAGEIF(A1:A8,"<>""",A1:A8)	Formula

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[ReeGiGi]

Thank you for your speedy response. That works perfectly!

Thank you, thank you, thank you )

 

[vogel997]

With two criteria:

19
20
9
2
0
15
20
14.17	Average
=AVERAGEIFS(A1:A8,A1:A8,"<>""",A1:A8,"<>0")	Formula

<colgroup><col><col></colgroup><tbody>
</tbody>