Sum to a max based on ranking, and use partial values

[JohnnyMango]

Hi all

This is my first post, so apologies if I do anything wrong. I'm pretty rusty with Excel, and am struggling to do even basic things, which is quite frustrating!

I've searched for an answer and this thread came close: Sum to a max value based on ranking

It gave a good answer on how to sum to a max value based on ranking. However, I would like to know if it's possible to do this, but to include a portion of the final cell value (if needed) to get to a total.

So, let's say you have 10 machines (or rows), ranked from cheapest to run to the most expensive (but the rows are not in that order), and each of them can produce 200 iPhones. However, you want 1100 iPhones, so, as in the thread linked above, the formula should display 200 for ranks 1 to 5, but then also 100 from the machine ranked 6 (and zero for machines ranked 7 to 10).

Can this be done using MIN? I can't think how to do it in a way that isn't circular, but I feel like I'm being a bit stupid.

I hope that made sense. (I'll install XL2BB before asking any further questions!)

Any help much appreciated.

Johnny

 

[jasonb75]

I've done similar recently, from your description I think that this should do it when you substitute the descriptions for your sheet ranges. Note that @Rank refers to the rank cell in the row with the formula rather than the whole rank column.

=MEDIAN(0,Capacity,1100-SUMIFS(capacity,rank,"<"&@rank))

If you have tied / equal ranks then it will be a little more complex.

 

[JohnnyMango]

I've done similar recently, from your description I think that this should do it when you substitute the descriptions for your sheet ranges. Note that @Rank refers to the rank cell in the row with the formula rather than the whole rank column.

=MEDIAN(0,Capacity,1100-SUMIFS(capacity,rank,"<"&@rank))

If you have tied / equal ranks then it will be a little more complex.

Thank you! That works really well. I really appreciate your help.

The problem of equal ranks may not be such an issue, but if it is do you have a way to deal with it? (Or is there a way to use a second criteria when ranking things? So in the event of a tie a second criteria will separate them, so that there are no ties?)

 

[jasonb75]

For tie breaks it would need to be something like this, for simplicity I've assumed that Capacity is in column A and rank in column B with headers in row 1 (which the second sumifs is looking at) first formula in row 2.

=MEDIAN(0,$A2,1100-(SUMIFS($A$2:$A$20,$B$2:$B$20,"<"&$B2)+SUMIFS($A$1:$A1,$B$1:$B1,$B2)))

This method should look at all rows for lower ranks and the rows above the formula for equal ranks. You will need to be careful with the $ symbols and the row offsets when setting this one up, if they are not aligned correctly then the results could be unpredictable.

If this doesn't work as needed then I would most likely need to see an example of the data layout to determine why.