Forum Discussion
Return Date based on a sum of the total so far
Is there a formula that I could use that would return the date from a dataset based on a total so far for other conditions. A bit like a lookup but takes the sum so far into account.
That's probably worded terribly, so I've given an example in the attached.
Listed at the top is the weather condition on a date, the date itself, and the number of Apples Picked on that date.
I would like a formula that would let me know on what date I picked the 12th Apple in Raining conditions. Can this be done without adding a further row(s) that have the cumulative total so far?
Thanks
4 Replies
An alternative solution using XLOOKUP rather than FILTER.
DatePickedλ(condition, threshold) = LET( Accumulateλ, LAMBDA(z, SCAN(0, z, LAMBDA(a, v, a + v))), total, Accumulateλ(IF(weather = condition, apples)), XLOOKUP(threshold, total, date, , 1) )This function will return the date. All you have to do is provide 'condition' and the 'threshold' (e.g. 5 and 12).
'Picked Lambda condition,threshold =LET( Accumulate, LAMBDA(a, v, a + v), filtered, FILTER(data, TAKE(data, 1) = condition), weather, TAKE(filtered, 1), dates, TAKE(DROP(filtered, 1), 1), apples, TAKE(filtered, -1), total, SCAN(0, apples, Accumulate), MIN(FILTER(dates, total >= threshold, 0)) )=LET(data,FILTER(B1:J3,B1:J1=C13),
cnt,SCAN(0,TAKE(data,-1),LAMBDA(ini,arr,ini+arr)),
INDEX(CHOOSEROWS(data,2),XMATCH(C12,cnt,1)))An alternative could be this formula if you work with Office 365 or Excel 2021. In cell C12 you can enter the number of apples and in cell C13 you can enter the weather condition.
I think adding two helper rows with the cumulative sum of dry and rainy days is MUCH easier than trying to create a complicated single formula.
