# (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

• 9.2 KB Views: 10

#### 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

• 11 KB Views: 6

#### Dotta

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