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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.