Forum Discussion
Second to last value from table with condition
Hi experts
Can you help me with the below?
I have a list of cash values in a table, example as follows by a rotating week number (1-4).
A ------------ B
1 ----------- 40
2------------- 30
3------------ 22
4 -----------56
1 -----------33
2 -----------15
3 -----------9
4------------40
1------------18
2------------79
3------------45
4------------61
What I want to do is have a formula to always pick out the second to last value corresponding to the week number. For example if I wanted the second to last value of week 4....
The formula would spit out = 40
If I wanted the second to last value of week 2....
The formula would spit out = 15
I have a formula to get the last value for each week... but second to last I am struggling with. I also need to mention the formula must ignore blank values!!!
Any help appreciated
Matt
40 Replies
This contains a very differently presented solution specific to Excel 365.
I converted the data to a table because I cannot read direct cell referencing . I then used FILTER to return relevant data from the specified columns and TAKE to select the final two entries.
= LET( recordNumber, SEQUENCE(ROWS(Trade)), dataFields, HSTACK(Trade[Pd / Rcvd], recordNumber), criteria, (Trade[Symbol1]=stock)*(Trade[Pd / Rcvd]<>""), returnedData, FILTER(dataFields, criteria), TAKE(returnedData, -2) )Hi, I'm using the file on my Android phone through Google drive hence there are lots of limitations, therefore please suggest ways which may seemingly work on my laptop (where it is accessed through Excel) and on my Android phone (where it is accessed through Google sheets. Thanks PeterBartholomew1
I only use Excel, and that on a Surface laptop. For years I had held the view that, if you were primarily interested in shared development and multi-platform use, Google Sheets would be the software of choice whereas, if you were a single user with serious programming objectives, Excel would suit you better.
Now everything appears up for grabs. I was startled at how rapidly Google responded to the game changing LET and LAMBDA. Equally, many of the recent Microsoft changes have had multiplatform use and sharing at their core. My guess is that you have the options of downloading Excel for Android or modifying the code to use INDEX and ROWS, in place of TAKE to return the final values and continuing to use Sheets.
If you make either or both work, I would be interested to know!
You can use the following formula to get the second to last value for a specific week number:
=INDEX(B:B,MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1)))Replace week_number with the week number you're interested in.
This is an array formula that uses the INDEX and MAX functions. Here's how it works:
- IF(A:A=week_number,ROW(B:B)-ROW(B1)+1) creates an array of row numbers where the week number matches week_number. The ROW(B:B)-ROW(B1)+1 part is used to adjust the row numbers to start at 1, since the formula will return an array starting from the first row.
- MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1)) finds the maximum row number in the array, which will be the row number of the last non-blank cell in the specified week.
- INDEX(B:B,MAX(IF(A:A=week_number,ROW(B:B)-ROW(B1)+1))) returns the value in column B at the row number found in step 2.
Make sure to enter this formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. This will add curly braces around the formula in the formula bar to indicate that it's an array formula.
You need to specify the Excel version. Otherwise you might get solutions like
= MAP({1;2;3;4}, LAMBDA(w, LET( criterion, (week=w)*(amount>0), matches, FILTER(amount, criterion), CHOOSEROWS(matches,-2) ) ) )A solution written for Excel 365 may have very little in common with earlier generations of spreadsheet.
=INDEX($B$1:$B$12,LARGE(IF(($A$1:$A$12=$D$1)*($B$1:$B$12<>""),ROW($1:$12)),2))
Is this what you are looking for? If you don't work with Office365 or 2021 you have to enter formula as arrayformula with ctrl+shift+enter.
I am getting #REF! error please help resolve OliverScheurich
Thanks so much for your reply. Sadly haven't got it to work like in your example.
I have knocked up an example spreadsheet. Hopefully this makes sense on how I am trying to use a formula for the second to last value in list?
I also have a formula for last value, this works fine as you will see.
Can you take a look and see what I mean from the cells highlighted in yellow?
Thank you again
Use
=INDEX($C$6:$C$58,LARGE(IF(($A$6:$A$58=F5)*($C$6:$C$58<>"")*($B$6:$B$58=G5),ROW($C6:$C58)-ROW($C$6)+1),2))
or
=INDEX($C$6:$C$58,LARGE(($A$6:$A$58=F5)*($B$6:$B$58=G5)*($C$6:$C$58<>"")*(ROW($C$6:$C$58)-ROW($C$6)+1),2))
If you don't have Office 2021 or Microsoft 365, confirm with Ctrl+Shift+Enter.
