(Partial) allocation starting from the smallest value.

[Dotta]

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.

 

[bosco_yip]

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

 

[Dotta]

It works!
Thank you ever so much. You've just saved me hours.