Average of Last 3 values in range

[Bradleeyb13]

I would like to find the average of the last 3 values in a certain range (rows) D3:AX3, this range will have many blank cells scatter between values and I would like to find the average for the last 3 values input into that range

 

[Peter_SSs]

Welcome to the MrExcel board!

See how this goes

23 05 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	38			3		5	6			8			100
Average last 3

Cell Formulas
Range	Formula
A3	A3	=AVERAGE(INDEX(D3:AX3,1,LARGE(IF(D3:AX3<>"",COLUMN(D3:AX3)-COLUMN(D3)+1),MIN(3,COUNT(D3:AX3)))):AX3)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Bradleeyb13]

Hi thanks for that, It comes up with “#VALUE!”
I forgot to mention that there is also some letters in that range aswell but I obviously only want the average for the numerical cells, would I have to change the range to every cell that only contains numbers or how do I filter only numbers? TIA

 

[Peter_SSs]

Are you sure that your numbers are actually numbers and not text values?
Could there be any #VALUE errors in the range D3:AX3?

This works for me

23 05 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	38			3		5	6		B	8		A	100
Average last 3

Cell Formulas
Range	Formula
A3	A3	=AVERAGE(INDEX(D3:AX3,1,LARGE(IF(ISNUMBER(D3:AX3),COLUMN(D3:AX3)-COLUMN(D3)+1),MIN(3,COUNT(D3:AX3)))):AX3)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Bradleeyb13]

Are you sure that your numbers are actually numbers and not text values?
Could there be any #VALUE errors in the range D3:AX3?

This works for me

23 05 20.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	38			3		5	6		B	8		A	100
Average last 3

Cell Formulas
Range	Formula
A3	A3	=AVERAGE(INDEX(D3:AX3,1,LARGE(IF(ISNUMBER(D3:AX3),COLUMN(D3:AX3)-COLUMN(D3)+1),MIN(3,COUNT(D3:AX3)))):AX3)
Press CTRL+SHIFT+ENTER to enter array formulas.

No errors in the range, I just deleted the formula and re-copied & pasted it now the error I am getting is "#NUM!",

This is a screenshot of the data in the range it is just numbers and "Y" with data being added week by week currently up to round 9 with round 10-24 all blank.

 

[Peter_SSs]

No errors in the range,

That answered my second question. What about my first question?

Are you sure that your numbers are actually numbers and not text values?

What does =ISNUMBER(T3) return if you put that in a empty cell?
A #NUM! error would occur with my formula if there are no numbers in the range, which again emphasises the need to check if they are actually numbers and not text.


Test of concept:
Start a fresh worksheet.
Click this icon at the top left of my Mini Sheet in post #4:

Select cell A3 in the blank worksheet
Paste
What appears in cell A3?

This is a screenshot of the data

We can't really tell much from a screenshot. What about range A1:V10 from your worksheet posted with XL2BB instead so we can check with your actual data?

 

[Bradleeyb13]

Trying to get the average for the last 3 values in a specific range (row), in this range there are blanks. I cant get XL2BB to work but basically the range starts in cell B2 and runs through to S2. I will drag the formula down to other rows once it works but some rows have up to 18 values and some rows 1 value and everywhere in between. TIA!

 

[jasonb75]

Were you able to get the formula suggested in your previous thread working? If you were then you can use the same one, simply change the ranges to match the new columns.

Average of Last 3 values in range

I would like to find the average of the last 3 values in a certain range (rows) D3:AX3, this range will have many blank cells scatter between values and I would like to find the average for the last 3 values input into that range

www.mrexcel.com

 

[Joe4]

I have combined your two threads together.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted. Simply continue on in your original question.

 

[Bradleeyb13]

Apologies about that,
Didn't get a resolution from my previous post but that was my fault for not chasing it up.
I have adjusted the suggested formula from above to reflect my new sheet and the formula reads as such..

=AVERAGE(INDEX(B2:S2,1,LARGE(IF(ISNUMBER(B2:S2),COLUMN(B2:S2)-COLUMN(B2)+1),MIN(3,COUNT(B2:S2)))):S2)

the result I get from this is "#NUM!".

I have also done the "=ISNUMBER" test and the returned value is "TRUE".

I cant work out how to get XL2BB to work, on my excel it doesn't allow me to select an area.