Forum Discussion

J-Des000's avatar
J-Des000
Brass Contributor
Jul 04, 2024

Displaying most recent value in a horizontal array?

Hi,

I have an array full of prices and 1 date column. I need to display the most recent value of each row's array that are all equal to today's date. I plan to sum all the numbers eventually.

Ex:

TODAY'S DATE: July 4th 2024

04-Jul$14.00$45.00 
78.00​
18-May$63.00$86.00
$92.00​
 
04-Jul$134.00$34.00$50.00 
04-Jul$25.00 
​
 


Output:

78
50
25

(Summed up this would be 153)

Thanks in advance!

excel
Excel on Mac
office 365
  • djclements's avatar
    djclements
    Jul 04, 2024

    J-Des000 You could try the following method to sort each row by column number (descending) to return a single column with the current prices (filtered by date = today):

     

    =LET(
    data, A2:E5,
    dates, TAKE(data,, 1),
    prices, DROP(data,, 1),
    n, COLUMNS(prices),
    cols, SEQUENCE(, n),
    currentPrices, TAKE(WRAPROWS(SORTBY(TOCOL(prices), TOCOL(IF(cols, SEQUENCE(ROWS(prices)))), 1, TOCOL(IF(prices <> "", cols, 0)), -1), n),, 1),
    FILTER(currentPrices, dates = TODAY(), 0)
)

     

    Adjust the data range as needed.

     

    Sample Results

  • djclements's avatar
    djclements
    Bronze Contributor
    Jul 04, 2024

    J-Des000 You could try the following method to sort each row by column number (descending) to return a single column with the current prices (filtered by date = today):

     

    =LET(
    data, A2:E5,
    dates, TAKE(data,, 1),
    prices, DROP(data,, 1),
    n, COLUMNS(prices),
    cols, SEQUENCE(, n),
    currentPrices, TAKE(WRAPROWS(SORTBY(TOCOL(prices), TOCOL(IF(cols, SEQUENCE(ROWS(prices)))), 1, TOCOL(IF(prices <> "", cols, 0)), -1), n),, 1),
    FILTER(currentPrices, dates = TODAY(), 0)
)

     

    Adjust the data range as needed.

     

    Sample Results

Resources