Forum Discussion

matt0020190

Brass Contributor

Dec 13, 2021

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------------- ...

excel

PeterBartholomew1

Silver Contributor

May 03, 2023

matt0020190

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)
  )

SatyaGupta_In

Brass Contributor

May 04, 2023

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