help for newbie

tubbzzzz

New Member
Joined
Mar 6, 2009
Messages
9
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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:
Upvote 0
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
 
Upvote 0
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 ?
thanks in advance
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hey Mick
Its looking good now
made some modifications

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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top