Sum n lowest values until the item frequency in another column reaches a limit

[kh999]

Hi Guys,

I need help with a formula. I want to dynamically sum the lowest N values. In the below data, how can i find the sum of the lowest costing 9 item units?

Additionally, is there a way to list these lowest costing 9 units dynamically in a new table in the same format with the # of units specified?

Item	# of Units	Cost per Unit	Total Item Cost
A	10	100	1000
B	4	11	44
C	2	22	44
D	6	33	198
E	1	22	22

 

[AlanSidman]

cannot visualize what you want. Please show a mocked up solution based upon the data you have provided.

 

[pecoflyer]

Cross post https://www.mrexcel.com/board/threa...ncy-in-another-column-reaches-a-limit.1140083

 

[kh999]

Sure. Sum of the lowest costing 9 item units = sum of (4 units of B + 2 units of C + 1 unit of E + 2 units of D) = 176.

The formula should sum the lowest values in column "cost per unit" until the corresponding sum in column "# of units" is 9.

Let me know if this is unclear.

Thanks for your help!

 

[Peter Bartholomew]

Regrettably, whilst I may have a solution, the techniques I use are not generally available.


The solution as it stands uses

= LET(
unitCost, SORTBY(Availability[Cost per Unit],Availability[Cost per Unit]),
cumulativeUnits, ACCUMULATE(SORTBY(Availability['# of Units],Availability[Cost per Unit])),
cappedUnits, IF(cumulativeUnits<Required, cumulativeUnits, Required),
units, DIFF(cappedUnits, 0),
itemCosts, units * unitCost,
SUM(itemCosts) )

LET: Microsoft 365 beta
SORTBY: Microsoft 365
ACCUMULATE: Charles Williams SpeedTools
DIFF: Charles Williams SpeedTools

Some refactoring is required!

 

[kh999]

Regrettably, whilst I may have a solution, the techniques I use are not generally available.

View attachment 69970
The solution as it stands uses

= LET(
unitCost, SORTBY(Availability[Cost per Unit],Availability[Cost per Unit]),
cumulativeUnits, ACCUMULATE(SORTBY(Availability['# of Units],Availability[Cost per Unit])),
cappedUnits, IF(cumulativeUnits<Required, cumulativeUnits, Required),
units, DIFF(cappedUnits, 0),
itemCosts, units * unitCost,
SUM(itemCosts) )

LET: Microsoft 365 beta
SORTBY: Microsoft 365
ACCUMULATE: Charles Williams SpeedTools
DIFF: Charles Williams SpeedTools

Some refactoring is required!

Yes that's the solution I want

 

[Peter Bartholomew]

An alternative solution that performs the accumulation with a sequence of partial sums using SUMIFS:
The advantage is that the formula does not require the data sorted by cost.


Since I gather you have chosen to cross-post, please do not neglect to inform each forum of any significant contribution made elsewhere.
Otherwise, a lot of time may simply be wasted.

 

[kh999]

Sure Peter! I have been able to solve using the 'small' function, which sorts the values first. I am using an extra tab but it's okay. Thanks for your prompt help!