Search code examples
excellistinputsumaggregate

Excel: aggregate elements of columns based on input

enter image description here

I want to create a dynamic analysis of the table above: the list is sorted by the value. It should aggregate the elements bottom up and stop if the input is reached. It should then return mne the sum of time. In this case, the input limit is 50 so 22+17 = 39 below 50, but 22+17+14 = 53 above 50. So it should give me 25+20 ) 45 min back.

I tried using the aggregate funktion but it returns an error as soon as I hit press.

Thanks in advance for any help!

Solution

  • I like tables because you have dynamic range references which adjust if you add/remove rows from the table.

    • Create a Table and named it tbl

    • Add two helper columns to the table creating a Reverse Running Total (doing a running total from the bottom up).

    • You can then use XLOOKUP to do the match.

    enter image description here

    Value RRT: =SUM(INDEX([Value],ROWS([Value])):[@Value])
time RRT: =SUM(INDEX([time],ROWS([time])):[@time])
result: =XLOOKUP(input,tbl[Value RRT],tbl[time RRT],"",-1,-1)

    For appearances, you can hide the RRT columns, or you can place them elsewhere on the worksheet. If you separate them from the Table, you'll have to include the table reference in the formula, and "fill down" the formula as far, or further as needed. So it won't be quite as dynamic as having them part of the table.