# Inventory allocation and variance help

#### Comp44

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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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)))

Last edited:

Replies
0
Views
148
Replies
0
Views
135
Replies
5
Views
177
Replies
1
Views
63
Replies
31
Views
387

1,196,325
Messages
6,014,647
Members
441,834
Latest member
GHOSTOF309

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back