• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

(Partial) allocation starting from the smallest value.

Dotta

New Member
Hi all
I wonder if someone can help me. Attached is a simplified example of my spreadsheet. People (rows) are given certain numbers of different fruit. I know how many they've eaten. Assuming they've started from type of fruit with the lowest total and fully cleared it before starting on another type I would like my spreadsheet to show how many they've got left.
Totals won't be changing, but I have ever increasing number of rows on my report, so a solution to be able to allocate the values automatically would be greatly appreciated. Many thanks for all your help.
 

Attachments

bosco_yip

Excel Ninja
Try,

In B8, array-formula copied across and down :

=IFERROR(1/(1/MAX(MIN(B2,SUM(AGGREGATE(15,6,$B2:$E2,ROW(INDIRECT("1:"&MATCH(B2,AGGREGATE(15,6,$B2:$E2,{1,2,3,4}),0)))))-$H2),0)),"")

Edit: array formula to be confirmed by pressing with CTRL+SHIFT+ENTER 3 keystrokes together instead of just ENTER

Regards
Bosco
 

Attachments

Top