Topping up a negative balance and then determing what to order

I have a simple spreadsheet that contains the numbers of parts in inventory, amount used in time period (in this case a year) and the remaining in inventory at the end of the year. In some cases the result is a positive number:

example a:

100 in inventory, 50 used with a result if 50 left at the end of the year

A1 100
A2 50
A3 50

A3 = A1-A2

Now what if the value in A2 was 200

A1 100
A2 200
A3 -100

Since this is forecasting balances at the end of the year it is ok to be negative.

How do I determine what I need to do to the value in A3 to make it 0 and then determine what to additionally add to meet the A2 number?

so, A3 is -100, need to add 100, so it is 0 and then add the value of A2 (200). So the end result should be 300.

can anyone help?

Hi, try this formula in A3:

Formula:

Please Login or Register  to view this content.

If we look at the formula in its pieces, we get:

If A1>=A2 (i.e. you didn't use as much as you had in inventory), then the formula remains as you have stated above (A1 - A2).
But if A1<A2 (i.e. you used more than you had in inventory), then an alternate formula is used (A2 - A1 + A2). This formula will switch the order of the original subtraction (to ensure a positive number is the result), and then adds the amount that was used within the period.

An alternate formula that would yield the same result is

Formula:

Please Login or Register  to view this content.

This makes the difference A1 - A2 positive regardless of the sign (absolute value), and then adds the amount used only if it is larger than the amount in inventory.

Both formulas will work, it is completely up to you which one you choose to use.

Hope this helps you

[QUOTE=ajryan88;3360867]Hi, try this formula in A3:

Formula:

Please Login or Register  to view this content.

If we look at the formula in its pieces, we get:

If A1>=A2 (i.e. you didn't use as much as you had in inventory), then the formula remains as you have stated above (A1 - A2).
But if A1<A2 (i.e. you used more than you had in inventory), then an alternate formula is used (A2 - A1 + A2). This formula will switch the order of the original subtraction (to ensure a positive number is the result), and then adds the amount that was used within the period.

An alternate formula that would yield the same result is

Formula:

Please Login or Register  to view this content.

This makes the difference A1 - A2 positive regardless of the sign (absolute value), and then adds the amount used only if it is larger than the amount in inventory.

Both formulas will work, it is completely up to you which one you choose to use.

Hope this helps you [/QUOTE

It seems to work for an overrun of the inventory - but not the reverse where you do not exceed the balance remaining. See below.

formula ex.GIF

any ideas??

perfect - thanks for the assist and I keep learning something new. Appreciate the help.

No worries.

Happy to help!