# help for newbie

#### tubbzzzz

##### New Member
Hi
what would be the best approach in excel to do the following
i have to estimate time taken for a machining operation on a lathe
example
material diameter is 50mm
finished diameter is 30mm
length of part is 100mm
surface speed is 100mtrs/min
feed is .25mm/per rev
depth of cut is 2mm(radially so actually 4mm from diameter)
formular for surface speed is ((1000*surface speed)/(Pi*diameter))
So for pass 1 50-4 =46 speed is 692rpm @ 100mtrs/rev
692*.25=173mm/min 100/173=.578 time for cut is .578mins
pass 2 diameter is 42 speed is 758rpm
pass 3 diameter is 38 speed is 837rpm

adding time taken for each pass calculate total time
help would be appreciated on this
many thanks

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### MickG

##### MrExcel MVP
Hi, Try this:-
Place the sheet data below in range("A1: B7").

Cutting speed Mts/Min-----100
Feed/Rev (mm)------------0.25
Depth of Cut (mm)-------- 2
O/D (mm)----------------- 55.25
Finish Dia (mm)------------ 30
Length Of Cut (mm)---------400
Total Time (sec)------------186.610611

Last edited:

#### MickG

##### MrExcel MVP
Hi, Ran out of editing Time !!!, Try this:-

Place the sheet data below in range("A1: B7").

Cutting speed Mts/Min-----100
Feed/Rev (mm)------------0.25
Depth of Cut (mm)-------- 2
O/D (mm)-----------------50
Finish Dia (mm)----------- 30
Length Of Cut (mm)-------400
Total Time (sec)----------143.2566

Click "Alt +F11" , VB window appears.
Paste the entire code below into the window.
Close window.
Change one of the input detals in column "B".
Click "B7".
The total time should now show in "B7".
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cs As Single, Feed As Single, DCut As Single, oD As Single, id As Single, Lg As Single
Dim pi, Tsec As Single, Ncuts As Single, Tot As Single
'Code operates when you click "B7"
If Target.Address <> "\$B\$7" Then Exit Sub
pi = Application.pi
Cs = Range("B1")
Feed = Range("B2")
DCut = Range("B3")
oD = Range("B4")
id = Range("B5")
Lg = Range("B6")
'Find number of Cuts
Ncuts = (oD - id) / (2 * DCut)
'Loops through the number of cuts to give Total Seconds
For Tsec = oD - (2 * DCut) To id Step -(DCut * 2)
'Formula for calculating time for each cut
Tot = Tot + Lg * pi * Tsec * 60 / (Cs * 1000)
Next Tsec

Range("A7") = "Total Time (sec)"
Range("B7") = Tot

End Sub``````
Check fig for accuracy.
Regards Mick

#### tubbzzzz

##### New Member
Hi Mick
I must be doing something wrong
I copied the 2 columns of data ,copied the macro into a workbook saved workbook but what links the data to the macro ?

#### MickG

##### MrExcel MVP
Hi, The postion of the data is most important, and that is column "B". Column "A" is just the Data Titles.
If you have the Titles in "A1 to A7" and the Data in "B1 to B7", and you have the code inserted in the VB Window, then all you need to do to activate the code is click cell "A7".
Obviously if you don't change at least one bit of the data in "B1 to B6" then cell "B7" value will remain the same.
I did it like this to make the running of the Macro easier, but if you would like to run it through the Macro Dialog Box, I can do that for you.
Regards Mick

#### tubbzzzz

##### New Member
Hi mick
need more hunderstanding cant make it work just by changing values in "b" column and clicking on B7 am using office 2007
but modified source to following

Sub Testimate()
Dim Cs As Single, Feed As Single, DCut As Single, oD As Single, id As Single, Lg As Single
Dim pi, Tsec As Single, Ncuts As Single, Tot As Single
'Code operates when you click "B7"
'If Target.Address <> "B7" Then Exit Sub
pi = Application.pi
Cs = Range("B1")
Feed = Range("B2")
DCut = Range("B3")
oD = Range("B4")
id = Range("B5")
Lg = Range("B6")
'Find number of Cuts
Ncuts = (oD - id) / (2 * DCut)
'Loops through the number of cuts to give Total Seconds
For Tsec = oD - (2 * DCut) To id Step -(DCut * 2)
'Formula for calculating time for each cut
Tot = Tot + Lg * pi * Tsec * 60 / (Cs * 1000)
Next Tsec

so it runs but would prefer it to change automatically couple of thing tho
1, if the passes do not divide equally say 3.22 for example this would need to roundup to 4 passes
2, how easy could it be modified if there were more than 1 diameter and length
for example stock diameter was 50 there was a diameter 25mm x 50 long
then it stepped up to 30mm diameter 25mm long

so in real terms from stock 50mm diameter 30mm diameter x 75mm long
then from stock 30mm diameter 25mm diameter x 50mm long

Also on lathe you sometimes have to limit maximum revs for safety because of weight of part so with calculation of surface speed actual might be 3500 rpm but for safety might be restricted to 2000 due to weight of part
so another column called max revs and some logic to alter calculation
to max revs allowed if rpm greater than
hope this makes sense
once again your help is appreciated

#### MickG

##### MrExcel MVP
Hi I can appreciate what your trying to do, as some years ago I worked in an Engineering Planning Office, Where we had the same problems.
We had a bespoke application called "Ceeqal", Which was supposed to perform the sort of timing operation your trying to resolve. Unfortunately it never worked. It took too long to program and didn't really cater for all the peripheral problems that take extra time. As a consequence we relied on personal Knowledge and experiance, and a strong liason with the craftsmen operating the machines to get accurate machining times.
I realise times have changed, but to obtain realistic times by calculation you would need to take into account a great many more factors and unless your working on very large runs, is it worth it ?.

With regard to your Question (1).
At the moment the code Rounds Down , but you could get it to round up.
Question (2), I suppose you would have to do two calculations.
I'm sorry this doesn't sound very positive, because it's quite interestiing to work on.
Maybe you situation is such that it could be viable.
Regards Mick

#### tubbzzzz

##### New Member
hi again Mick
with the formula (1000*Cs)/(Pi*Cutting diameter) for RPM
With a variable for Max allowed RPM how would this be integrated into
the macro
eg
max prm 1500
cut 1 rpm 1250 max rpm 1500 output rpm 1250
cut 2 rpm 1450 max rpm 1500 output rpm 1450
cut 3 rpm 1600 max rpm 1500 output rpm 1500
any subsequent passes would be at 1500
Also how would the roundup be added

In my spread sheet I will have to look at a method of selecting how many diameters and lengths there are and add this by repeating your macro
did you have any thoughts on why the macro would not auto run for me ,a setting somewhere prehaps
thanks again for yout time and help

#### MickG

##### MrExcel MVP
Hi, I am not familiar with XL 2007, so cannot say why you "Selection_change event, does not work. In reality you want a Command Button on your sheet,to run the code.
I can explain this proceedure for ealier version, but am not sure what difference 2007 has.
You can always record a Macro with a Key Combination to run it ,and then paste any code inside it.
The piece of code below with Calculate the time for a number of passes over a fixed length (as you original post). All the details (Cut Dia, Rpm etc will be displayed in columns "C to I" showing information for each Cut.
If you then perform another operation to reduce the Dia over a smaller length for example, and enter these requirements in Column "B". Then run the code again, the new result will be posted under the previous Results Columns "C to I". With the Total Machining time being shown in minutes in Cell "A11".
You can have as many of these operations as you like, To give total M/C Time.
NB:- There are a few notes within the code to give you a better understanding of what happening.
There was an error in my previous calculation, in case you find you Fig don't tally.
As you will see I have now taken account of an uneven numbers of cuts,by adding an extra cut where required and reducing the final cut depth. I have also limited the RPM to 1500, when it is reached.
Code:
``````Sub Machine()
'Multi Cuts
Dim Cs As Single, Feed As Single, DCut As Single, oD As Single, id As Single, Lg As Single
Dim pi, Tsec As Single, Ncuts, Tot As Single, c, Last As Long
Dim rpm, TimOneRev, TotNumRevs, TotTime, CutDia, Temp
Dim Rng As Range, Dn As Range, oSum As Single
Last = Range("E" & Rows.Count).End(xlUp).Row
Last = IIf(Last > 1, Last + 1, Last)
pi = Application.pi
Cs = Range("B1")
Feed = Range("B2")
DCut = Range("B3")
oD = Range("B4")
id = Range("B5")
Lg = Range("B6")
Ncuts = (oD - id) / (2 * DCut)
'If the Total depth of cuts Divided by the Depth of cut has
'a remainder then the number of cuts is increase by one
'& the Depth of final cut reduced accordingly

If Ncuts - Int(Ncuts) <> 0 Then
Temp = (Ncuts - Int(Ncuts)) '* DCut * 2
Ncuts = Int(Ncuts) + 1
End If

For Tsec = Ncuts To 1 Step -1
c = c + 1
If Tsec = 1 And Temp <> "" Then
CutDia = oD - (2 * DCut * c) + ((DCut - (DCut * Temp)) * 2)
Else
CutDia = oD - (2 * DCut * c)
End If

rpm = (Cs * 1000) / (pi * CutDia)
'if rpm  > 1500 then 1500
rpm = IIf(rpm > 1500, 1500, rpm)
'Time for  1 Rev
TimOneRev = 60 / rpm
'Total Revs Required
TotNumRevs = Lg / Feed
'Totalling Revs per pass
TotTime = TotNumRevs * TimOneRev
Tot = Tot + TotTime

Cells(Last, 3) = "Dia of Cut"
Cells(c + Last, 3) = CutDia
Cells(Last, 4) = "RPM"
Cells(c + Last, 4) = rpm
Cells(Last, 5) = "Time for 1 rev"
Cells(c + Last, 5) = TimOneRev
Cells(Last, 6) = "Tot Turns/Cut"
Cells(c + Last, 6) = TotNumRevs
Cells(Last, 7) = "Tot Time/Cut"
Cells(c + Last, 7) = TotTime
Next Tsec
Range("H" & Last) = "Total Time (sec)"
Range("H" & Last + 1) = Tot
Range("B7") = Tot
Range("I" & Last) = "Total Cuts"
Range("I" & Last + 1) = c

' Totals all the times
Set Rng = Range(Range("H1"), Range("H" & Rows.Count).End(xlUp))
For Each Dn In Rng
If IsNumeric(Dn) Then
oSum = oSum + Dn.Value
End If
Next Dn
Range("A10").Value = "Total M/C Time (min)"
Range("A11").Value = oSum / 60
End Sub``````
Try this out against your calculated results see what you think.
Regards Mick

#### tubbzzzz

##### New Member
Hey Mick
Its looking good now

Option Explicit
Sub Machine()
'Multi Cuts
Dim Cs As Single, Feed As Single, DCut As Single, oD As Single, id As Single, Lg As Single
Dim pi, Tsec As Single, Ncuts, Tot As Single, c, Last As Long
Dim rpm, TimOneRev, TotNumRevs, TotTime, CutDia, Temp
Dim Rng As Range, Dn As Range, oSum As Single
Dim Mrevs As Single
Last = Range("E" & Rows.Count).End(xlUp).Row
Last = IIf(Last > 1, Last + 1, Last)
Range("A1") = "Cutting speed Mts/Min"
Range("A2") = "Feed/Rev (mm)"
Range("A3") = "Depth of Cut (mm)"
Range("A4") = "O/D (mm)"
Range("A5") = "Finish Dia (mm)"
Range("A6") = "Length Of Cut (mm)"
Range("A7") = "Max rpm"
Range("A8") = "Total Time (sec)"
pi = Application.pi
Cs = Range("B1")
Feed = Range("B2")
DCut = Range("B3")
oD = Range("B4")
id = Range("B5")
Lg = Range("B6")
Mrevs = Range("B7")
Ncuts = (oD - id) / (2 * DCut)
'If the Total depth of cuts Divided by the Depth of cut has
'a remainder then the number of cuts is increase by one
'& the Depth of final cut reduced accordingly
If Ncuts - Int(Ncuts) <> 0 Then
Temp = (Ncuts - Int(Ncuts)) '* DCut * 2
Ncuts = Int(Ncuts) + 1
End If
For Tsec = Ncuts To 1 Step -1
c = c + 1
If Tsec = 1 And Temp <> "" Then
CutDia = oD - (2 * DCut * c) + ((DCut - (DCut * Temp)) * 2)
Else
CutDia = oD - (2 * DCut * c)
End If
rpm = (Cs * 1000) / (pi * CutDia)
'if rpm > 1500 then 1500
rpm = IIf(rpm > Mrevs, Mrevs, rpm)
'Time for 1 Rev
TimOneRev = 60 / rpm
'Total Revs Required
TotNumRevs = Lg / Feed
'Totalling Revs per pass
TotTime = TotNumRevs * TimOneRev
Tot = Tot + TotTime
Cells(Last, 3) = "Dia of Cut"
Cells(c + Last, 3) = CutDia
Cells(Last, 4) = "RPM"
Cells(c + Last, 4) = rpm
Cells(Last, 5) = "Time for 1 rev(Secs)"
Cells(c + Last, 5) = TimOneRev
Cells(Last, 6) = "Tot Turns/Cut"
Cells(c + Last, 6) = TotNumRevs
Cells(Last, 7) = "Tot Time/Cut"
Cells(c + Last, 7) = TotTime
Next Tsec
Range("H" & Last) = "Op Time (sec)"
Range("H" & Last + 1) = Tot
'Range("B8") = Tot
Range("I" & Last) = "Total Cuts"
Range("I" & Last + 1) = c

' Totals all the times
Set Rng = Range(Range("H1"), Range("H" & Rows.Count).End(xlUp))
For Each Dn In Rng
If IsNumeric(Dn) Then
oSum = oSum + Dn.Value
End If
Next Dn
Range("A10").Value = "Total M/C Time (min)"
Range("B10").Value = oSum / 60
Range("B8").Value = oSum
End Sub

Would like to take things a step further if its possible and you have more time If i set up a columns with material groups
with surface speeds and feeds and depths of cut
an example as follows :-
Material Cs Dcut Feed
Carbon steel 250 2.5 .3
Stainless 175 2 .25
Aluminium 400 4 .35
Brass 350 3 .35
If we had another row called material("A9") and input a material into the "(B9") column could we pull the data across to our other "B" columns
What do you think

Once again thanks for all your help

### Forum statistics

1,191,170
Messages
5,985,061
Members
439,937
Latest member
MAlhash ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back