Calculating weight based on measurements

[mpdsal]

Greetings,

I have a situation where I need to calculate inventory. In my case I
have an item stored in pounds but when it's removed from inventory
it's in measurements of inches, for example 2x3, or 5x6 etc. Does
anyone have a straightforward approach to doing this so that I see
starting inventory amount, amount removed, and current balance in
inventory. FYI 10.5 x 40 inches = 1 pound of material. So an employeed
would go to this material and cut a piece x by y and use it until it's
gone or even return it.

Thanks!

 

[Cimjet]

Hi MPDSAL
Maybe someone else can come up with a better idea.
I would convert all size to square inches 1 pound = 420 sq.inch. multiply by
your inventory then deduct from usage. You can bring it back to pound at the
end.
For calculation, I would keep numbers in separate cells.
HTH
John

 

[Gord Dibben]

A1 enter 1

A2 enter =A1+1 then drag/copy down to A420

B1 enter =A1/420 then double-click to copy down to B20

In C1 enter =VLOOKUP(C1,$A$1:$B$420,2)

In D1 enter =2*3

C1 returns .014286

In D1 enter =21.4 * 6.3

C1 returns .319048


Gord Dibben MS Excel MVP

 

[mpdsal]

A1 enter 1

A2 enter  =A1+1 then drag/copy down to A420

B1 enter  =A1/420  then double-click to copy down to B20

In C1 enter  =VLOOKUP(C1,$A$1:$B$420,2)

In D1 enter  =2*3  

C1 returns  .014286

In D1 enter  =21.4 * 6.3

C1 returns  .319048

Gord Dibben     MS Excel MVP







- Show quoted text -

I'll try it. Thanks

Mark

 

[mpdsal]

Hi MPDSAL
Maybe someone else can come up with a better idea.
 I would convert all size to square inches 1 pound = 420 sq.inch. multiply by
your inventory then deduct from usage. You can bring it back to pound at the
end.
For calculation, I would keep numbers in separate cells.
HTH







- Show quoted text -

Is the 420 sq in per pound ratio for solids or does it refer to
liquids or gas? Does it apply to a pound of anything?

Thanks

Mark

 

[Cimjet]

Hi Mark
Find attached a link to a sample file below.
http://cjoint.com/?0lzrkhhYwXS
Write back if you need information.

Is the 420 sq in per pound ratio for solids or does it refer to

liquids or gas? Does it apply to a pound of anything?
This information comes from you

FYI 10.5 x 40 inches = 1 pound of material.

To get the sq. in, you multiply X by Y, hope that helps

Regards
John

Hi MPDSAL
Maybe someone else can come up with a better idea.
I would convert all size to square inches 1 pound = 420 sq.inch. multiply by
your inventory then deduct from usage. You can bring it back to pound at the
end.
For calculation, I would keep numbers in separate cells.
HTH







- Show quoted text -

Is the 420 sq in per pound ratio for solids or does it refer to
liquids or gas? Does it apply to a pound of anything?

Thanks

Mark

 

[Rick Rothstein]

You have a straight proportioning problem. Assuming the material is uniform
in density and is always of the same thickness, you have the relationship
that 1 pound = 420 square inches. So, setting up the proportioning ratio...

1 X
------ = -------------------
420 Area Removed

and solving for 'X' yields this equation...

X = (Area Removed) / 420

So, for your 5x6 example, that is 30 square inches and the above equation
becomes...

X = 30 / 420

which equals 0.0714 pounds of material. So, basically, your formula for the
cell would be...

= A1/420

where A1 contains the area of the removed section. However, since you told
us nothing about your layout, I can't give you a more detailed or focused
response than this.

Rick Rothstein (MVP - Excel)



"mpdsal" wrote in message

Greetings,

I have a situation where I need to calculate inventory. In my case I
have an item stored in pounds but when it's removed from inventory
it's in measurements of inches, for example 2x3, or 5x6 etc. Does
anyone have a straightforward approach to doing this so that I see
starting inventory amount, amount removed, and current balance in
inventory. FYI 10.5 x 40 inches = 1 pound of material. So an employeed
would go to this material and cut a piece x by y and use it until it's
gone or even return it.

Thanks!

 

[mpdsal]

You have a straight proportioning problem. Assuming the material is uniform
in density and is always of the same thickness, you have the relationship
that 1 pound = 420 square inches. So, setting up the proportioning ratio...

  1                  X
------  =  -------------------
420       Area Removed

and solving for 'X' yields this equation...

X = (Area Removed) / 420

So, for your 5x6 example, that is 30 square inches and the above equation
becomes...

X = 30 / 420

which equals 0.0714 pounds of material. So, basically, your formula for the
cell would be...

= A1/420

where A1 contains the area of the removed section. However, since you told
us nothing about your layout, I can't give you a more detailed or focused
response than this.

Rick Rothstein (MVP - Excel)

"mpdsal"  wrote in message


Greetings,

I have a situation where I need to calculate inventory. In my case I
have an item stored in pounds but when it's removed from inventory
it's in measurements of inches, for example 2x3, or 5x6 etc. Does
anyone have a straightforward approach to doing this so that I see
starting inventory amount, amount removed, and current balance in
inventory. FYI 10.5 x 40 inches = 1 pound of material. So an employeed
would go to this material and cut a piece x by y and use it until it's
gone or even return it.

Thanks!

Rick,

I understand what you are saying. It makes sense. Your solution will
work and I have set up those formulas in a separate sheet. So in Sheet
one I will have Products A-X that is stored in inventory in pounds.
When Product A is used it is cut in dimensions of X by Y and then I
have a formula that will show me the changes (reductions or added
back) in the amount in inventory. Using your ratio approach will work
however how do I denote a negative? In other words when I type in my
dimensions as a negative I get an error in the lookup because all the
values are positive. So what additional steps/formuals should I use so
when I remove stock it shows as a negative, or a positive if I return
stock. Does this make sense? Example:

Product A = 1 lb in inventory
I use 5 x 6 = .0714 lb
Current balance of Product A = .9286


Thank you very much.

Mark