Forum Discussion
Second to last value from table with condition
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!
- I'm interested in using Excel on both devices phone & laptop but once you upload files on Google drive and access them on any device other than from which it is uploaded, it will open by default on Google sheets. This is the precise reason that I have to use Google sheet when I open files on mobile phone
