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

 

[Bradleeyb13]

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

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: View attachment 91995
Select cell A3 in the blank worksheet
Paste
What appears in cell A3?


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?

I have just got this formula working within a new sheet, the only issue I have now is that it counts all cells not excluding blanks or zeros.
Any idea to fix this?
I added "ISNUMBER" behind average but that just returns "0"


=AVERAGE(INDEX(B2:S2,0,COLUMNS(B2:S2)-(W1-1)):INDEX(B2:S2,0,COLUMNS(B2:S2)))

Thanks!

 

[Bradleeyb13]

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.

If I get no further replies am I able to make a new thread?

 

[Peter_SSs]

If I get no further replies am I able to make a new thread?

If the question is ongoing #12 of the Forum Rules precludes that. However, that rule also advises about 'bumping' your thread occasionally to get it at or near the top of the forum. Of course not all forum threads get a resolution either.

For some reason I seem to have stopped getting notifications of responses to this thread so I hadn't seen that there were further posts. Luckily I just happened to see it today.

Since it quite some time since I was dealing with this it is no longer fresh in my mind. Could we have some sample data, expected results and explanation in relation to that sample data?

It would be best with XL2BB. You have indicated problems with the add-in. What exactly is the problem? Could this be it? XL2BB Icons greyed out

 

[Bradleeyb13]

If the question is ongoing #12 of the Forum Rules precludes that. However, that rule also advises about 'bumping' your thread occasionally to get it at or near the top of the forum. Of course not all forum threads get a resolution either.

For some reason I seem to have stopped getting notifications of responses to this thread so I hadn't seen that there were further posts. Luckily I just happened to see it today.

Since it quite some time since I was dealing with this it is no longer fresh in my mind. Could we have some sample data, expected results and explanation in relation to that sample data?

It would be best with XL2BB. You have indicated problems with the add-in. What exactly is the problem? Could this be it? XL2BB Icons greyed out

Okay I got that working, thanks!

This is the data I am dealing with..

Disposals Auto 2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Player	R1	R2	R3	R4	R5	R6	R7	R8	R9	R10	R11	R12	R13	R14	R16	R17	R18	R19	R20	Tot
2	Baker, Oskar	13	13	18	20	11	15	20	15	15	20	15	22	4	14				12		227
3	Bontempelli, Marcus	25	24	28	25	23	31	19	32	25	30	27	23	31	32	27	29	31	29	27	518
WB

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1048576	Cell Value	contains "Y E S"	text	NO
1:1048576	Cell Value	contains "Yes"	text	NO
1:1048576	Cell Value	beginning with "No"	text	NO
1:1048576	Cell Value	<0	text	NO
1:1048576	Cell	contains a blank value	text	NO
1:1048576	Cell	contains an error	text	NO

With 2 different players we have player "Baker, Oskar" with a handful of blanks scattered across his range where I would like to get the average for the last 3 games he has played in (N2,O2,S2). The second player "Bontempelli, Marcus" has played the last 3 games and is easy to create a formula to work out his last 3 average.

Thanks!

 

[Peter_SSs]

Glad you were able to get XL2BB going.

So, doesn't the formula suggested in post #2 do what you want if you adjust it to your ranges?

23 07 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Player	R1	R2	R3	R4	R5	R6	R7	R8	R9	R10	R11	R12	R13	R14	R16	R17	R18	R19	R20	Tot		Average last 3
2	Baker, Oskar	13	13	18	20	11	15	20	15	15	20	15	22	4	14				12		227		10
3	Bontempelli, Marcus	25	24	28	25	23	31	19	32	25	30	27	23	31	32	27	29	31	29	27	518		29
Average

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

 

[Bradleeyb13]

Glad you were able to get XL2BB going.

So, doesn't the formula suggested in post #2 do what you want if you adjust it to your ranges?

23 07 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Player	R1	R2	R3	R4	R5	R6	R7	R8	R9	R10	R11	R12	R13	R14	R16	R17	R18	R19	R20	Tot		Average last 3
2	Baker, Oskar	13	13	18	20	11	15	20	15	15	20	15	22	4	14				12		227		10
3	Bontempelli, Marcus	25	24	28	25	23	31	19	32	25	30	27	23	31	32	27	29	31	29	27	518		29
Average

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

The error I get on this is "#VALUE!",

Looking back on the thread I conducted the "=ISNUMBER" on one of the random cells and got a return of "FALSE".
How do I turn the values to numbers? When I change the number format to number it still remains false..

 

[Bradleeyb13]

The error I get on this is "#VALUE!",

Looking back on the thread I conducted the "=ISNUMBER" on one of the random cells and got a return of "FALSE".
How do I turn the values to numbers? When I change the number format to number it still remains false..

Further more to add to the confusion, I thought I would test more than 1 cell with "=ISNUMBER" and I have found that cell H2 returns a "FALSE" where as cell M2 returns "TRUE". How can this be if all data is from the same source? how can I convert all to numbers?
Cheers!

 

[Peter_SSs]

ll data is from the same source?

What is the source of the "numbers"?
I would be trying to fix the data at the source as 'numbers' that are not numbers will make things difficult.

When I change the number format to number it still remains false.

Changing the format of the cell does not change the nature of the underlying value in the cell which will remain text.

 

[Bradleeyb13]

What is the source of the "numbers"?
I would be trying to fix the data at the source as 'numbers' that are not numbers will make things difficult.


Changing the format of the cell does not change the nature of the underlying value in the cell which will remain text.

It is an external source (data from a website), more specifically a table from the website.

 

[Peter_SSs]

It is an external source (data from a website), more specifically a table from the website.

All the values that look like numbers in your post #14 sample come across from XL2BB as actual numbers. Does that mean that XL2BB has coerced them to numbers or is that data not representative of your actual "mixed" data?
If not representative, can you post some with XL2BB that is representative?