Excel help, work out circumferance, over and over..

peige

peige

peige

Soldato
Joined
18 Oct 2002
Posts
3,714
Location
Sussex
Its been years since i've had to do a macro type formula but I need one now, in basic terms, I need to specify two diameters, upper and lower and then work out every circumference between them in 0.1 (this will be a variable) increments, basically giving me the total travel for a spinning part between the outside diameter and the inside diameter, ie all those circumferances added together.

Any pointers ? I'm currently reading up :cool:
 
I like the simple approach....

D1 the diameter of small circle
D2 the diameter of large circle
Var the variable distance between tracks

It takes D1 works out circumference, add on Var to D1 and works out circumference and so on until it reaches D2 then adds them all together.

A bit like this Works out Circumferences

Not without a few flaws, like if the Variable distance does not fit exactly, however you can just take the running total of the reading before it rounds it off to D2

If the 2000 odd circumferences it can measure is not enough then it can be easily increased by copying the formulas down further and changes the Total reference at the top

Diddy
 
Last edited:
Given that the number you actually want is just the sum of circumferences it would be easier to just to this as the sum of an arithmetic series.

i.e.

Sum of the series is n(a1+an)/2 where n is the number of terms and a1 and an are the first and last terms. The number of terms if you have a difference of 0.1 is (an-a1)*10+1. If you have a different increment then replace 10 with 1/x where x is your variable.

So if
Smallest diameter is 5
Largest diameter is 8

number of terms is (8-5)*10 + 1 = 31
so the sum is 31X(8+5)/2 = 201.5

So your sum of circumferences is 201.5 * pi.


To make this more useful, if you put the initial circumference in A1, the larger one in A2 and the increment in A3 then the formula you need is:

=PI()*(((A2-A1)/A3)+1)*(A1+A2)/2
 
Last edited:
Thanks both of you :)

Ricky, your solution is elequent and simple, Diddyman, you've done it the long way that I had in mind but couldn't do :)

The aspect that I didn't mention is that I need to calculate the time to cover each circle, the machine maintains a certain surface speed, ie 40m/min. So each circle is effectively done at a different spindle speed. So i think i can take your way Diddyman and add a new line that works out the time to cover each distance at the surface speed that the spindle will be running at that diameter

ie 40m/min will be Circumferance /((40 x 1000) / (pi * D)) will give me the time to travel round a D diameter circle.

Add all those together and I think i've got something that will tell me how long it will take to face the front of a billet of metal off at a given surface speed, well more or less :)

Thanks !
 
As each circle takes a different amount of time I don't think that can work.. in straight line terms the spindle accelerates as the tool point gets closer to the centre of the part.
So
100mm dia cut at 40 m/min means 127rpm and will take 0.404 seconds aprox
50mm dia cat at 40 m/min means 254rpm and will take 0.618 seconds aprox

Unless i'm missing something, which is quite possible given how simple your first solution was :o
 
Oops :p

Well, you get what I mean !

/edit.. but thinking on you may be correct, if the machine does its job it will cover the whole distance whilst maintaining 40 meters a minute.. i'm going to think about that in a dark room for a bit !
 
Last edited:
You may well be correct, i'm trying to get my head around it but if the machine does its job then the tip should be traveling over the job at 40 m/min so the time will be the total distance in meters /40 .. i'm going to think about this for a bit !
 
Last edited:
Have a internet cookie, your spot on.. thanks so much for such a simple solution, a new quoting tool for me is born !

Anything that makes quoting easier is great, I hate quoting !

cheers :)
 
You must log in or register to reply here.
Back
Top Bottom