# Break down a total quantity into columns

#### joshc

##### New Member
Hello All!

Got a query which I hope someone can help with.. I have a spreadsheet that I need to create for work due to a new process. Essentially what I need help on is a formula which automatically breaks down the allocation (column B) into packs (columns D-M). Below is a shortened version of what we use - could be up to 100 packs per store and over 1000 stores for example.

 Allocation Pack Size Pack 1 Pack 2 Pack 3 Pack 4 Pack 5 Pack 6 Pack 7 Pack 8 Pack 9 Pack 10 Store 1 750 75 Store 2 250 75 Store 3 165 75 Store 4 50 75 Store 5 75 75 Store 6 315 75 Store 7 105 75 Store 8 50 75 Store 9 175 75 Store 10 200 75

<tbody>
</tbody>

The maximum pack size is in column C and each pack should not exceed this value. If we look at the first 2 rows of data it would be broken down as per the below.

 Allocation Pack Size Pack 1 Pack 2 Pack 3 Pack 4 Pack 5 Pack 6 Pack 7 Pack 8 Pack 9 Pack 10 Store 1 750 75 75 75 75 75 75 75 75 75 75 75 Store 2 250 75 75 75 75 25 Store 3 165 75 Store 4 50 75 Store 5 75 75 Store 6 315 75 Store 7 105 75 Store 8 50 75 Store 9 175 75 Store 10 200 75

<tbody>
</tbody>

Any help appreciated!

Cheers
Josh

#### Joe4

Place this formula in D2:
=MIN(\$C2,\$B2)
copy down for all of column D

Place this formula in E2:
=MIN(\$C2,\$B2-SUM(\$D2:D2))

and copy to all the rest of your cells in columns E through M

#### joshc

##### New Member
Perfect, thanks Joe.

You are welcome.