Break down a total quantity into columns

joshc

New Member
Joined
Sep 6, 2017
Messages
5
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.

AllocationPack SizePack 1Pack 2Pack 3Pack 4Pack 5Pack 6Pack 7Pack 8Pack 9Pack 10
Store 175075
Store 225075
Store 316575
Store 45075
Store 57575
Store 631575
Store 710575
Store 85075
Store 917575
Store 1020075

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

AllocationPack SizePack 1Pack 2Pack 3Pack 4Pack 5Pack 6Pack 7Pack 8Pack 9Pack 10
Store 17507575757575757575757575
Store 22507575757525
Store 316575
Store 45075
Store 57575
Store 631575
Store 710575
Store 85075
Store 917575
Store 1020075

<tbody>
</tbody>


Any help appreciated!

Cheers
Josh
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

Threads
1,082,607
Messages
5,366,591
Members
400,904
Latest member
srija

Some videos you may like

This Week's Hot Topics

Top