Excel Running total reaching a threshold and choosing a corresponding date

Question

Hi,&nbsp;I have a row of Dates in a Quarter (around 90 separate days) and below each date is a figure for the number of products sent that day. i want to cumulatively add (running total) the number of Products sent each day from Day 1 until they reach a cumulative threshold of 3 and then copy the date that this threshold is met to a field at the end of the row.I have tried all sorts of formulas and used Chat GPT but no luck.&nbsp;It doesn't matter what the value in the cell is, when it reaches the cumulative value count of 3.

excelonlineadvisor · Answer

DaveyMac&nbsp;I didn't understand it completely. But, Is this what you want ? (Yellow highlighted part)

peterbartholomew1 · Answer

DaveyMac&nbsp;This may or may not be what you require!&nbsp; It uses 365 and I do not 'do' backward compatibility.&nbsp;= LET(
    cumulative, SCAN(0, sent, SUM),
    XLOOKUP(3, cumulative, VSTACK(date, cumulative), "not reached", 1)
  )&nbsp;

peterbartholomew1 · Answer

Despite having said I do not do backward compatibility, I had better remove the eta-reduced Lambda function for now!= LET(
    SUMλ, LAMBDA(x,y, SUM(x,y)),
    cumulative, SCAN(0, sent, SUMλ),
    XLOOKUP(3, cumulative, VSTACK(date, cumulative), "not reached", 1)
  )

Forum Discussion

Excel Running total reaching a threshold and choosing a corresponding date

Share

Resources