need help on a formula

[bzax33]

Hi. Brand new to this website. Was told I can ask Excel questions here.
I have 3 columns of numbers: 1 is last weeks inventory, 2 is this weeks inventory, and 3 is the difference (eg, 12 apples last week, 6 this week = 6 sold). But sometimes, if a pallet of apples is added then the total this week will be higher than last week. I'm trying to find a formula that will find the difference between the 2 weeks and if the difference is a negative number (i.e. a pallet was added) then will add 96, 172, etc (the sizes of pallets) until it becomes a positive number.
Is this possible?

 

[Tom Urtis]

Was told I can ask Excel questions here.

...a rampant internet rumor.



Your question is a bit confusing. You talk of apples as individual items and then as pallets as maybe the items to be calculated. Either way, the 96 or 172 part does not make sense, how are those numbers derived, how would anyoen know which size of pallet which have a common denominator not being the 96 number.

Also, what does "until it becomes a positive number" really mean to you, is the difference being just 1 greater than teh other truly sufficient?

Maybe some sample data would help with understanding what you are working with.

 

[larrythestag]

Would this work for you?

=IF(A1-B1<0,((A1-B1)+(96))+96*(ROUND((A1-B1)/96,0)*-1),A1-B1)

 

[bzax33]

Sorry about that. Me try to describe it better:
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 might 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'm trying to find a formula that can both figure out the difference between the 2 columns and add a set number if the original difference is a negative.
I hope this isn't too confusing.
Thanks!