Formula for last 5 numbers and transpose to different sheets.

M

musclebunny

New Member
Joined
Jan 5, 2023
Messages
9
Platform
  1. Web
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi,
For the Last Row in a given column
Excel Formula: 
=SUMPRODUCT(MAX((ROW(A1:A200))*(A1:A200<>"")))
 
Upvote 0
James006 said:
Hi,
For the Last Row in a given column
Excel Formula: 
=SUMPRODUCT(MAX((ROW(A1:A200))*(A1:A200<>"")))
Click to expand...
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.
 
Upvote 0
Assuming the formula in Cell A1 on Sheet1 is
Book1
ABCDEFGHIJKLMNOPQRST
11849155646423443434381719114011106
Sheet1
Cell Formulas
RangeFormula
A1:T1A1=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
ABCDE
14946443840
Sheet3
Cell Formulas
RangeFormula
A1:E1A1=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
Excel Formula: 
=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.
 
Upvote 0
jdellasala said:
Assuming the formula in Cell A1 on Sheet1 is
Book1
ABCDEFGHIJKLMNOPQRST
11849155646423443434381719114011106
Sheet1
Cell Formulas
RangeFormula
A1:T1A1=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
ABCDE
14946443840
Sheet3
Cell Formulas
RangeFormula
A1:E1A1=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
Excel Formula: 
=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.
Click to expand...
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.
 
Upvote 0
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).

1681813340545.png
 

Attachments

  • 1681813222965.png
    1681813222965.png
    34.9 KB · Views: 3
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Golf Simulator League - Calculating Golf Handicaps for Google Sheets
2
Replies
12
Views
2K
Fluff
Fluff
E
  • Question
Combine COUNTIF, OFFSET and SUM in GoogleSheets
Replies
2
Views
414
ErnestFichtner
E
M
  • Question
Count the number of cells in a column containing a dash for Google sheets
Replies
0
Views
689
mariehz
M
J
  • Question
Find all or certain formulas in a Workbook
Replies
1
Views
426
johnnyL
J
S
  • Question
Extract last non empty cell of row (ignoring empty cell containing formula) Google Sheets.
Replies
6
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top