Need formula to calculate lenght of a roll. I have all info.

[Guest]

My company often needs to know how much material there is on a particular
roll and seeing as some of them are over 500 feet in lenght when they get
here, rolling them out is not an option.

Using the following example, I would appreciate if you could tell me whether
this can be formulated or if I need to do this in vba.

This is what I did to measure one, as you can see it's effective but
impractical.

Total diameter - 210 mm, Core diameter (there is no material in here) 89mm,
Thickness of material 0.25mm.

I started by calculating the radius (105mm) minus the core's radius 45mm)
giving me a useable radius of 60mm. Divided that by .25 giving me 240
"loops" around the core.

This is where it gets impractical. I created 240 cells where each cell in
column A would be Pi*Diameter, and increasing the diameter by .5mm every
cell, representing the added thickness of .25mm on both sides of the core.
Then added all 240 cells giving me the total length in material.

I know in VBA I could use a loop function and it would be fine, but I'm
really hoping I can have it as a 1line formula that takes the information
entered in various cells and calculate on the spot.

Am I dreaming? Help is much appreciated.

 

[Niek Otten]

Can't you just take the outer and the inner one and average the others? You
then don't have to bother about the number of layers, at least not in the
number of cells you need.

 

[Guest]

Not sure I understand.

The inner is 188.49, the outer is 563.92. Are you saying that I take the
average of thsoe 2 numbers which is 376.205? If So it's close, but the
actual lenght was 355 and that 20 feet could make a difference. I'm looking
for something as exact as possible but I'm seriously impressed with how close
that was

 

[Guest]

Google "calculating length of a spiral" for a formula

 

[Guest]

I did what Charlie suggested to you and found this useful link. It has the
exact formula that you need.

http://www.newton.dep.anl.gov/askasci/math99/math99015.htm

Alok

 

[Niek Otten]

You seem to have started with a diameter of 60. Shouldn't that be 90?

I'll think a bit more about a simpler but exact formula

 

[Niek Otten]

That seems to confirm my approach of taking the average. Unless something
else comes up, I'll stop trying to find something more clever!