Inventory allocation and variance help

[Comp44]

Hello all,

I'm stumped on this one and I'm sure I'm making it more complicated than it needs to be. Hope you can help.
I'm trying to find a formula to calculate inventory availability. This is a 2 part problem.
1. Need a formula to pull figures out of column E total inventory until there's nothing left

2. Need a formula to show a percentage in column D (including negative percentage once the inventory runs out). Running into an issue with the #DIV/0! error when it comes to locations where the system is expecting 0, so 0 would be allocated.

Blank example

	Column A	Column B	Column C	Column D	Column E
1	Site	System Expect Amount	Allocation	Delta	Total Inventory
2	Warehouse 1	20000			100,000
3	Warehouse 2	40000
4	Warehouse 3	25000
5	Warehouse 4	30000
6	Warehouse 5	15000

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

How I need it to be:

	Column A	Column B	Column C	Column D	Column E
1	Site	System Expect Amount	Allocation	Delta	Total Inventory
2	Warehouse 1	20000	20000	0%	100,000
3	Warehouse 2	40000	40000	0%
4	Warehouse 3	25000	25000	0%
5	Warehouse 4	30000	15000	-50%
6	Warehouse 5	15000	0	-100%

<tbody>
</tbody><colgroup><col><col><col><col><col><col></colgroup>

Thanks in advance for your help!

 

[igold]

Hi,

Do these do what you want

Cell C2 and copied down: =IF(E$2 > SUM(B$2:B2),B2,IF(SUM(B$2:B2) > E$2,IF(SUM(C1:C$2) < E$2,E$2-SUM(C1:C$2),0)))

<e$2,e$2-sum(c1:c$2),0)))
<e$2,e$2-sum(c1:c$2),0)))
Cell D2 and copied down: =IF(C2=0,-1,((B2-C2)/B2)*-1)


HTH

igold</e$2,e$2-sum(c1:c$2),0)))
</e$2,e$2-sum(c1:c$2),0)))