Formula for last 5 numbers and transpose to different sheets.

[musclebunny]

Question:
I have sheet1 which contains rows and columns of numbers.
I want to find the last 5 numbers in each row and have the results into Sheet2, Sheet3, Sheet4 etc.

To find the last 5 numbers, currently I'm using the formula example: =offset($A1,0,COUNT(A1:Q1)-5,,5)
I don't think it's a good formula because it only work on one line at a time.
Also I can't get the formula to transpose the results to different sheets. eg =Sheet1!offset($A1,0,COUNT(A1:Q1)-5,,5) won't work, neither does the 'IMPORTRANGE' formula work.

Questions:
1) What is a better formula which could get results for last 5 numbers per row and also do several rows/columns simultaneously? (if that's possible).
2) What formula for transposing results to Sheets1, 2, 3, 4 etc?

I'm using Googlesheets, often however Excel formulas will work.

 

[James006]

Hi,
For the Last Row in a given column

=SUMPRODUCT(MAX((ROW(A1:A200))*(A1:A200<>"")))

 

[musclebunny]

Hi,
For the Last Row in a given column

=SUMPRODUCT(MAX((ROW(A1:A200))*(A1:A200<>"")))

Hi, I'm not working from columns but rows.
It's not the last single value either, it is last several values in a row.
If by luck the formula can encompass the last several values in several rows, that's even better.

Then, if I'm really lucky if I can have the results into different sheets that would be a bonaza.

Thx.

 

[jdellasala]

Assuming the formula in Cell A1 on Sheet1 is

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1	8	49	15	5	6	46	4	23	44	34	34	38	17	19	11	40	11	10	6
Sheet1

Cell Formulas
Range	Formula
A1:T1	A1	=RANDARRAY(1,20,1,50,1)
Dynamic array formulas.

resulting in a single row of 20 columns with an integer of between 1 and 50, the formula on any other sheet

Book1
	A	B	C	D	E
1	49	46	44	38	40
Sheet3

Cell Formulas
Range	Formula
A1:E1	A1	=FILTER(Sheet1!A1#,Sheet1!A1#>=LARGE(Sheet1!A1#,5))
Dynamic array formulas.

will result in a row containing the five largest values in Row 1, Sheet1 (A1:T1). If you need the values in one cell, use

=TEXTJOIN(", ",TRUE,FILTER(Sheet1!A1#,Sheet1!A1#>=LARGE(Sheet1!A1#,5)))

(Yeah, I was surprised that no conversion from numbers to text was needed too!).
Use TRANSPOSE if you need the results in a column.

 

[musclebunny]

Assuming the formula in Cell A1 on Sheet1 is

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	1	8	49	15	5	6	46	4	23	44	34	34	38	17	19	11	40	11	10	6
Sheet1

Cell Formulas
Range	Formula
A1:T1	A1	=RANDARRAY(1,20,1,50,1)
Dynamic array formulas.

resulting in a single row of 20 columns with an integer of between 1 and 50, the formula on any other sheet

Book1
	A	B	C	D	E
1	49	46	44	38	40
Sheet3

Cell Formulas
Range	Formula
A1:E1	A1	=FILTER(Sheet1!A1#,Sheet1!A1#>=LARGE(Sheet1!A1#,5))
Dynamic array formulas.

will result in a row containing the five largest values in Row 1, Sheet1 (A1:T1). If you need the values in one cell, use

=TEXTJOIN(", ",TRUE,FILTER(Sheet1!A1#,Sheet1!A1#>=LARGE(Sheet1!A1#,5)))

(Yeah, I was surprised that no conversion from numbers to text was needed too!).
Use TRANSPOSE if you need the results in a column.

Thank you for reply.
The random generator had me perplexed for a short bit but got that sussed. Was an interesting distraction.

The formula: =FILTER(Sheet1!A1#,Sheet1!A1#>=LARGE(Sheet1!A1#,5)) didn't work out as:
(a) I was not looking for LARGEST but looking for the LAST 5.
(Actually I want the last 60 values but to keep it simple I'm just asking here for 5 as it's easier for this exercise).

(b) The formula is not working for me in googlesheets, I'm getting 'Formula Parse Error'.
I tried tweaking around with it but still couldn't get it to work.
I thought if I could get your formula to work, then I could perhaps adapt it from there, alas no luck.

 

[musclebunny]

So I thought to try ChatGPT and see what it would come up with.
It suggested several times the following array formula, alas although prompting it numerous times the formula ChatGPT offered had a problem as well, it only returned "8" (formula in cell M8).