Calculation of Average of last 3 non #N/A values

[Kutkan]

Values Average Last 3 non #n/a

12
14
13 13
11 13
45 23
#N/A 23
44 33
13 34
41 33
#N/A 33
33 29
22 32
#N/A 32
45 33

Is there a way to do this automatically with a function?

 

[Aladin Akyurek]

Values Average Last 3 non #n/a

12
14
13 13
11 13
45 23
#N/A 23
44 33
13 34
41 33
#N/A 33
33 29
22 32
#N/A 32
45 33

Is there a way to do this automatically with a function?

Let A2:A15 house the sample you provided.

B2, control+shift+enter, not just enter, and copy down:

=IF(COUNT($A$2:A2)>=3,AVERAGE(IF(ROW($A$2:A2)>=
    LARGE(IF(ISNUMBER($A$2:A2),ROW($A$2:A2)),3),
      IF(ISNUMBER($A$2:A2),$A$2:A2))),"")

 

[Kutkan]

Many thanks Alaaddin.

Working further that night I came with a solution, but it is more convoluted than yours. Does not work with the first 2 cells where you do not have 3 values

When you let A2:A15 have the values

B4, control+shift+enter, and copy down:

=AVERAGE(LOOKUP(LARGE((ROW(INDIRECT(1&":"&COUNTA($A$2:$A4))))*NOT(ISNA($A$2:$A4));{1;2;3});ROW(INDIRECT(1&":"&COUNTA($A$2:$A4)));$A$2:$A4))

 

[Aladin Akyurek]

Many thanks Alaaddin.

You are welcome.

Working further that night I came with a solution, but it is more convoluted than yours. Does not work with the first 2 cells where you do not have 3 values

When you let A2:A15 have the values

B4, control+shift+enter, and copy down:

=AVERAGE(LOOKUP(LARGE((ROW(INDIRECT(1&":"&COUNTA($A$2:$A4))))*NOT(ISNA($A$2:$A4));{1;2;3});ROW(INDIRECT(1&":"&COUNTA($A$2:$A4)));$A$2:$A4))

Looks expensive too.