Averaging the last 3 numbers

[Madcat]

G'day

I'm looking for a formula so that I can average the last 3 numbers.

R01  	  R02  	  R03  	  R04  	  R05  	  R06  	  R07  	  R08 	  R09 	 R09 - CC 	 R09 -TE 	 R10 	 R11 	 R12 	 R12 - SoO I 	 R13 	 R14 	 R14 - SoO II 	 R15 	 R16 	 R17 	 R18 	 R19 	 R20 	 
 30.1 	 17.6 	 29.1 	 27.0 	 17.8 	 25.9 	 19.8 	 43.2 	 -   	 10.1 	 -   	 19.0 	 -   	 15.4 	 10.4 	 17.5 	 -   	 19.1 	 13.7 	 -   	 -   	 -   	 -   	 -   	 -   	 -   	 -   	 -

For example the last 3 numbers are
R15: 13.7
R14 - SoO II: 19.1
R14: -
R13: 17.5

So the average should be 16.76.

Thanks for your help.

Regards
Madcat

 

[Madcat]

Could someone please have a look at my request?

 

[anandvarma]

try this

=AVERAGE(INDIRECT(CHAR(COUNT(A1:Z1)+64)&1),INDIRECT(CHAR(COUNT(A1:Z1)+63)&1),INDIRECT(CHAR(COUNT(A1:Z1)+62)&1))

it is hard coded
put your data on row 1 and test it

 

[Aladin Akyurek]

Could someone please have a look at my request?

Control+shift+enter, not just enter...

=AVERAGE(IF(ROW(R1:R20)>=LARGE(IF(ISNUMBER(R1:R20),ROW(R1:R20)),3),IF(ISNUMBER(R1:R20),R1:R20)))

 

[Madcat]

Control+shift+enter, not just enter...

=AVERAGE(IF(ROW(R1:R20)>=LARGE(IF(ISNUMBER(R1:R20),ROW(R1:R20)),3),IF(ISNUMBER(R1:R20),R1:R20)))

Thanks for the reply Aladin.

The Rounds go up to R24, I don't know if that helps or not?

=AVERAGE(IF(ROW(N3:AO3)>=LARGE(IF(ISNUMBER(N3:AO3),ROW(N3:AO3)),3),IF(ISNUMBER(N3:AO3),N3:AO3)))

I have tried Control+shift+enter and it's come up 11.3, where it should be 16.8.

  R01  	  R02  	  R03  	  R04  	  R05  	  R06  	  R07  	  R08 	  R09 	 R09 - CC   R09 -TE 	 R10 	 R11 	 R12 	 R12 - SoO I 	 R13 	 R14 	 R14 - SoO II 	 R15 	 R16 	 R17 	 R18 	 R19 	 R20 	 R21 	 R22 	 R23 	 R24 
 30.1    17.6 	 29.1 	 27.0 	 17.8 	 25.9 	 19.8 	 43.2 	    -    10.1 	          -   	19.0 	 -   	15.4 	 10.4 	        17.5 	 -   	 19.1 	        13.7 	 -   	 -   	 -   	 -   	 -   	 -   	 -   	 -   	 -

 

[Aladin Akyurek]

Thanks for the reply Aladin.

The Rounds go up to R24, I don't know if that helps or not?

=AVERAGE(IF(ROW(N3:AO3)>=LARGE(IF(ISNUMBER(N3:AO3),ROW(N3:AO3)),3),IF(ISNUMBER(N3:AO3),N3:AO3)))

I have tried Control+shift+enter and it's come up 11.3, where it should be 16.8.

  R01        R02        R03        R04        R05        R06        R07        R08       R09      R09 - CC   R09 -TE      R10      R11      R12      R12 - SoO I      R13      R14      R14 - SoO II      R15      R16      R17      R18      R19      R20      R21      R22      R23      R24 
 30.1    17.6      29.1      27.0      17.8      25.9      19.8      43.2         -    10.1               -       19.0      -       15.4      10.4             17.5      -        19.1             13.7      -        -        -        -        -        -        -        -        -

Control+shift+enter, not just enter:

=AVERAGE(IF(COLUMN(N3:AO3)>=LARGE(IF(ISNUMBER(N3:AO3),COLUMN(N3:AO3)),3),IF(ISNUMBER(N3:AO3),N3:AO3)))

 

[Madcat]

Control+shift+enter, not just enter:

=AVERAGE(IF(COLUMN(N3:AO3)>=LARGE(IF(ISNUMBER(N3:AO3),COLUMN(N3:AO3)),3),IF(ISNUMBER(N3:AO3),N3:AO3)))

Thanks again Aladin but now I only get a - .

 

[Aladin Akyurek]

Thanks again Aladin but now I only get a - .

What is it, a value in the range of interest or what?