Formula that adds different amounts depending on criteria

[bzax33]

I need help with a formula I would use to estimate sales based off of inventory.

I'm trying to find a formula that can both figure out the difference between 2 columns and, if the number is a negative, add multiples of 50 until the negative is a positive.

Here is an example:

In column A there is the inventory item (e.g. apples, oranges, etc)
In column B there is last week's inventory of that item (e.g. 50)
In column C there is this week's inventory of that item (e.g. 25)

Currently I have a simple formula that works out the difference (in this case 25) and that is my "items sold" number. However sometimes these items get restocked in the middle of the week and THIS week's inventory is higher than LAST week's (e.g. last week's inventory was 50 and this week's is 75). Now I know that apples come in boxes of 50 so if I see a negative number (last week's 50 minus this week's 75 is -25) I add 1 box of 50 to that negative number and come out with the total of 25 sold for the week.
If last weeks inventory was 50 and this week's inventory is 125 (for a total of -75) I would add 100 (2 boxes of apples brought into stock) and then come out with a total of 25 sold for the week.

I hope this isn't too confusing.

 

[Sahak]

Hi Bzax33;

Try this:
Suppose
A2 = “Apples”
B2 = 50 (quantity in box)
C2 = 50 (last week inventory)
D2 = 125 (this week inventory)
E2 = formula:

 =IF(D2>C2,(C2-D2)+((INT((C2-D2)*-1/B2)+1)*B2),C2-D2)

will = 25

 

[bzax33]

Haha, that works! Thanks so much

 

[Sahak]

You are welcome