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]

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?

When I ran “=isnumber” on individual cellsI found mixed results, when running on the range (B2:T2) as a whole it came back as “FALSE”.
after using “text to columns” on all columns I again ran “=isnumber” on all individual cells and they all came back “TRUE” although the formula still didn’t work?

 

[Peter_SSs]

When I ran “=isnumber” on individual cellsI found mixed results,

That did not really address my question about whether XL2BB changed the nature of your sample data or not.

Was the sample data in post 14 produced from a mixture of True/False ISNUMBER data, or was the original data in that case all ISNUMBER True before you used XL2BB?
To be able to investigate the best way forward, I would want to get hold of some actual 'mixed' data so I can determine just what it is that is causing ISNUMBER to return False in some cells.

 

[Bradleeyb13]

That did not really address my question about whether XL2BB changed the nature of your sample data or not.

Was the sample data in post 14 produced from a mixture of True/False ISNUMBER data, or was the original data in that case all ISNUMBER True before you used XL2BB?
To be able to investigate the best way forward, I would want to get hold of some actual 'mixed' data so I can determine just what it is that is causing ISNUMBER to return False in some cells.

I think I was able to find an issue, below is an updated sheet where on the right hand side I have done an "=ISNUMBER" test on all relevant cells, the first row I have replaced the blanks with "0" and they all return back as "TRUE", the second row all cells in the range were filled anyway so all return "TRUE". The third row has a mix of numbers and blanks, with the blanks left they are the cells returning as "FALSE".

Is there a way to add an excluding "0" to this formula? =AVERAGE(INDEX(B2:T2,1,LARGE(IF(B2:T2<>"",COLUMN(B2:T2)-COLUMN(B2)+1),MIN(3,COUNT(B2:T2)))):T2)

I would have thought the 0s would have been included within the last 3 average and the average for the first row would become 4 (12 / 3) but it is still showing the "#VALUE!"

I have also placed this formula in with the "#VALUE!" error underneath "3GM AVG"

Hope this helps!

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	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	Player	R1	R2	R3	R4	R5	R6	R7	R8	R9	R10	R11	R12	R13	R14	R16	R17	R18	R19	R20	Tot	3GM AVG	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	Baker, Oskar	13	13	18	20	11	15	20	15	15	20	15	22	4	14	0	0	0	12	0	227	#VALUE!	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
3	Bontempelli, Marcus	25	24	28	25	23	31	19	32	25	30	27	23	31	32	27	29	31	29	27	518	#VALUE!	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
4	Bruce, Josh	14	17	11	14	8								10					6	3	83	#VALUE!	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
WB

Cell Formulas
Range	Formula
V2:V4	V2	=AVERAGE(INDEX(B2:T2,1,LARGE(IF(B2:T2<>"",COLUMN(B2:T2)-COLUMN(B2)+1),MIN(3,COUNT(B2:T2)))):T2)
W2:AO4	W2	=ISNUMBER(B2)

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

 

[Peter_SSs]

he third row has a mix of numbers and blanks, with the blanks left they are the cells returning as "FALSE".

Well, you would expect blank cells to return FALSE, since a blank is not a number.

I think XL2BB is changing the data since when I copy your mini sheet to my blank sheet, the formulas in column V are automatically returning the correct answers.
4 for row 2 as you described above (0+0+12)/3
29 for row 3 (31+29+27)/3
6.333 for row 4 (10+6+3)/3

What do each of these formulas return if you put them in some blank cells, noting that R4 is one of the 'blank' cells?
=LEN(R4)
=CHAR(R4)
=R4=""

 

[Bradleeyb13]

Well, you would expect blank cells to return FALSE, since a blank is not a number.

I think XL2BB is changing the data since when I copy your mini sheet to my blank sheet, the formulas in column V are automatically returning the correct answers.
4 for row 2 as you described above (0+0+12)/3
29 for row 3 (31+29+27)/3
6.333 for row 4 (10+6+3)/3

What do each of these formulas return if you put them in some blank cells, noting that R4 is one of the 'blank' cells?
=LEN(R4)
=CHAR(R4)
=R4=""

What do each of these formulas return if you put them in some blank cells, noting that R4 is one of the 'blank' cells?
=LEN(R4) - "0"
=CHAR(R4) - "#VALUE!"
=R4="" - "TRUE"

 

[Peter_SSs]

Thanks for the formula returns. However, that means I cannot tell why the formula I suggested is not working.

Are you able to upload a small sample file where the column V formula is not working (with any sensitive data disguised or removed) to one of DropBox, OneDrive, Google Drive etc and post a publicly shared link here so that I can take a look at an actual problem workbook?

Edit: Oops perhaps I do know what the problem is. What happens if you edit the V2 formula and confirm with Ctrl+Shift+Enter, not just Enter and then copy down?

 

[Bradleeyb13]

Thanks for the formula returns. However, that means I cannot tell why the formula I suggested is not working.

Are you able to upload a small sample file where the column V formula is not working (with any sensitive data disguised or removed) to one of DropBox, OneDrive, Google Drive etc and post a publicly shared link here so that I can take a look at an actual problem workbook?

Edit: Oops perhaps I do know what the problem is. What happens if you edit the V2 formula and confirm with Ctrl+Shift+Enter, not just Enter and then copy down?

Oh wow yes that actually works!

Thankyou so much!
May I just ask why that happens and what doing Ctrl+Shift+Enter means?

 

[Peter_SSs]

Ctrl+Shift+Enter makes the formula into an 'array formula' so that it processes calculation(s) on all the values in an array not just one. Some more information and examples here.
With Microsoft 365, which I use, there is no longer a need to use Ctrl+Shift+Enter which is why I forgot about it for your version (& often do )

Anyway, glad we got there in the end.

 

[Bradleeyb13]

Ctrl+Shift+Enter makes the formula into an 'array formula' so that it processes calculation(s) on all the values in an array not just one. Some more information and examples here.
With Microsoft 365, which I use, there is no longer a need to use Ctrl+Shift+Enter which is why I forgot about it for your version (& often do )

Anyway, glad we got there in the end.

Thanks heaps!
You are the best and I will mark this as finsihed!
Hopefully one day I can wrap my head around excel as much as you can!

Thanks again

 

[Peter_SSs]

You are very welcome. Thanks for your kind comments.

I must be getting a little tired/confused. Looking back over the thread, although I did not specifically mention it in my text, I had included the fact that the formula needed to be confirmed with Ctrl+Shift+Enter. See this from post #15 (it was similarly shown in post #2)

I have therefore changed the 'Mark as solution' to post #15.