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]

I think instances like 75.10 are going to be curious. If it is entered as a number, the trailing zero will be dropped, meaning that it will not be possible to differentiate 75.1 from 75.10.
The entries would need to be entered as text to maintain the trailing zeroes.
Otherwise, the entries you need to be made in a different manner.

Leo, can you tell us more how you differentiate 1 and 10 packs?

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1258648-need-help-with-formulae-for-inventory-scenario.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[leohothunk]

Hi All,


I'm really very sorry that I didnt go through with the rules... as I was in bit pressure to work on the task (on which am getting help from you all).
Now my work is in control and bit cool...


My very first task is to go through the rules now.


Yes, I also posted my query on another forum, it was due to the work pressure.


Sure will edit my post with the link and update/reply all accordingly.




Thanks
Leo

 

[leohothunk]

I think instances like 75.10 are going to be curious. If it is entered as a number, the trailing zero will be dropped, meaning that it will not be possible to differentiate 75.1 from 75.10.
The entries would need to be entered as text to maintain the trailing zeroes.
Otherwise, the entries you need to be made in a different manner.

Leo, can you tell us more how you differentiate 1 and 10 packs?

Hi Joe,

Better will take the In-stock boxes as less quantity which will be 100 boxes. Each box has 12 packs.

Scenario -

First customer, I will give 11 boxes and 4 packs... balance should be 88 boxes and 8 packs.
now the stock balance is 88.8.
2nd customer, I will give 12 boxes and 7 packs ... balance should be 76 boxes and 1 pack
now the stock balance is 76.1
3rd customer, I will give 13 boxes and 3 packs... now the balance is 62 boxes and 10 packs
now the stock balance is 62.10
to 4th customer I will give only 11 packs (no boxes)... now the balance is 61 boxes and 11 packs
now the stock balance is 61.11

and so on....

hope you got the differentiate in 1 and 10 packs...

Thanks
Leo

 

[jtakw]

Ok, really, Did you try my formula in Post # 10 ??

 

[leohothunk]

Ok, really, Did you try my formula in Post # 10 ??

Yes, just now tried with your formula "My Formula" and its working perfect as expected...
Thank you very much sir.

Also thanks to all.

Thanks
Leo

 

[jtakw]

You're welcome, welcome to the forum.

Glad we can help.