Formula for inventory scenario

[leohothunk]

Need guidance with formula for inventory scenario

Hi Team,

Need your expertise with one formula...

Scenario -
I have 1700 no of boxes.
each box has 12 packs/pieces inside.
If I sold 75.4 (sometimes the boxes and decimal number will different as per the order)
balance should be 1624.8 its by manually

Need an formula for this scenario to keep maintain the inventory.

Excel table - (below calculation is as per manual)
ColumnA - No of Boxes are 1700
ColumnB - No of Boxes sold are 75.4
ColumnC - Balance boxes 1624.8


Thanks
Leo

 

[Joe4]

Welcome to the Board!

Isn't it just putting a formula in column C that subtracts column B from column A?, i.e.

=A2-B2

Or is there more to it than that, that you haven't told us about yet?

If the issue is that you want the previous rows ending value (column C) to be the new current rows starting value, then just place this in cell A3:

=C2

and copy the formulas in column A and C down for all your rows.

 

[philwojo]

Joe, I think the OP is looking to take in to account the 12 pieces per box. 1700-75.4 would be 1624.6, not the 1624.8 they are expecting. I don't have time to work through it just pointing it out is all.

 

[Joe4]

Joe, I think the OP is looking to take in to account the 12 pieces per box. 1700-75.4 would be 1624.6, not the 1624.8 they are expecting. I don't have time to work through it just pointing it out is all.

If that is the case, then it appears that the decimal part of the number really isn't a decimal at all, but rather a number between 1 and 11, representing the fractional part of the box (12ths).
Is that correct, Leo?

If so, any chance we can use some "helper" columns (we can always hide them)?
Otherwise, the formula might get to be a bit messy.

 

[leohothunk]

If that is the case, then it appears that the decimal part of the number really isn't a decimal at all, but rather a number between 1 and 11, representing the fractional part of the box (12ths).
Is that correct, Leo?

If so, any chance we can use some "helper" columns (we can always hide them)?
Otherwise, the formula might get to be a bit messy.

Hi Joe4,

Yes correct, decimal is nothing but number between 1 and 12...

Scenario or example -
Monday -
One customer asks me to deliver the 32 cookie boxes and 4 cookie packs

Tuesday -
Another customer asks me to deliver the 26 cookie boxes and 2 cookie packs

Tuesday evening or Wednesday -
When I calculate or maintain the inventory in excel, I should get the sold boxes as 32+26=58 boxes and 4+2=6 packs
Take as I was having a stock of 100 cookie boxes... (in one box there are 12 cookie packs) so with above scenario inventory balance should be 41 boxes + 6 packs.

Hope you got my query

Thanks
Aarif

 

[Joe4]

Can you answer my last questions here?

If so, any chance we can use some "helper" columns (we can always hide them)?
Otherwise, the formula might get to be a bit messy.

Otherwise, I am going to give you a huge monstrosity of a formula that may scare you!

 

[Joe4]

If your starting value was in cell A2 and the boxes sold were in B2, here is what the formula I came up with to put in C2 would look like:

=VALUE(INT(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10)))/12)&"."&MOD(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10))),12))

Using "helper" columns, I would convert the numbers in columns A and B to the number of "packs", like this:

Column A packs (put in cell E2):

=INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10)

Column B packs (put in cell F2):

=INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10)

Total difference in packs (put in cell G2):

=E2-F2

Convert difference to boxes (put in cell C2):

=VALUE(INT(G2/12) & "." & MOD(G2,12))

You can then elect to hide columns E, F, and G if you like (no need to display them).

 

[leohothunk]

If your starting value was in cell A2 and the boxes sold were in B2, here is what the formula I came up with to put in C2 would look like:

=VALUE(INT(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10)))/12)&"."&MOD(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10))),12))

Using "helper" columns, I would convert the numbers in columns A and B to the number of "packs", like this:

Column A packs (put in cell E2):

=INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10)

Column B packs (put in cell F2):

=INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10)

Total difference in packs (put in cell G2):

=E2-F2

Convert difference to boxes (put in cell C2):

=VALUE(INT(G2/12) & "." & MOD(G2,12))

You can then elect to hide columns E, F, and G if you like (no need to display them).

Hi Joe4,
I do not have any words to say...
I mean, am not sure how to thank you...
with the first formula I'm able to get the right balance

[COLOR=#333333]=VALUE(INT(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10)))/12)&"."&MOD(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10))),12))[/COLOR]

Thank you very much sir...

Checking for the rest of the formulas, will confirm you soon.

Thanks
Leo

 

[Joe4]

You are welcome.

The other formulas do the same thing as the first formula, it is just broken up into smaller bits, which makes it a little easier to maintain, if you have need to modify the formulas at all.

 

[jtakw]

Hi,

I'm sorry, but I find that the formula you're choosing is providing incorrect results for my sample below, row D6 and D14.

Try my formula in C2:

Book1
	A	B	C	D
1	Inventory	Sold	My Formula	Joe4's Formula
2	1700.0	75.4	1624.8	1624.8
3	1700	75.11	1624.1	1624.1
4	1700	75.4	1624.8	1624.8
5	1700	75.1	1624.11	1624.11
6	1700	75.2	1624.10	1624.1
7	1700	75.3	1624.9	1624.9
8	1700	75.4	1624.8	1624.8
9	1700	75.5	1624.7	1624.7
10	1700	75.6	1624.6	1624.6
11	1700	75.7	1624.5	1624.5
12	1700	75.8	1624.4	1624.4
13	1700	75.9	1624.3	1624.3
14	1700	75.10	1624.2	1624.11
15	1700	75.11	1624.1	1624.1
16	1700	75.0	1625.0	1625
17	1700.4	75.4	1625.0	1625
18	1700.4	75	1625.4	1625.4
Sheet444

Cell Formulas
Range	Formula
C2	=INT((INT(A2)*12+IFERROR(MID(A2,FIND(".",A2)+1,2),0)-(INT(B2)*12+IFERROR(MID(B2,FIND(".",B2)+1,2),0)))/12)&"."&ROUND(MOD((INT(A2)*12+IFERROR(MID(A2,FIND(".",A2)+1,2),0)-(INT(B2)*12+IFERROR(MID(B2,FIND(".",B2)+1,2),0)))/12,1)*12,0)
D2	=VALUE(INT(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10)))/12)&"."&MOD(((INT(A2)*12+IF(ROUND(A2-INT(A2),2)=0.11,11,ROUND(A2-INT(A2),1)*10))-(INT(B2)*12+IF(ROUND(B2-INT(B2),2)=0.11,11,ROUND(B2-INT(B2),1)*10))),12))