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

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.

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.

--
Kind regards,

Niek Otten

"Marc" <[email protected]> wrote in message
news:[email protected]...
> 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.

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

"Niek Otten" wrote:

> 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.
>
> --
> Kind regards,
>
> Niek Otten
>
> "Marc" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

Google "calculating length of a spiral" for a formula

"Marc" wrote:

> 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
>
> "Niek Otten" wrote:
>
> > 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.
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "Marc" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

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/askasc.../math99015.htm

Alok

"Marc" wrote:

> 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
>
> "Niek Otten" wrote:
>
> > 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.
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> >
> > "Marc" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
> >

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

--
Kind regards,

Niek Otten

"Charlie" <[email protected]> wrote in message
news:[email protected]...
> Google "calculating length of a spiral" for a formula
>
> "Marc" wrote:
>
>> 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
>>
>> "Niek Otten" wrote:
>>
>> > 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.
>> >
>> > --
>> > Kind regards,
>> >
>> > Niek Otten
>> >
>> > "Marc" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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.
>> >
>> >
>> >

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

--
Kind regards,

Niek Otten

"Alok" <[email protected]> wrote in message
news:[email protected]...
>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/askasc.../math99015.htm
>
> Alok
>
> "Marc" wrote:
>
>> 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
>>
>> "Niek Otten" wrote:
>>
>> > 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.
>> >
>> > --
>> > Kind regards,
>> >
>> > Niek Otten
>> >
>> > "Marc" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > 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.
>> >
>> >
>> >