Help with Tan()!

[zach454]

I have been working on this particular problem for some time now. I work in a machine shop and I'm trying to use some forumla's to make our lives easier, but my anwser don't come out the same as theirs.

If you go to:

http://www.zakgear.com/Over_Pins.html

You will see there is several calculations, and I have most figured out except for when you get to the:

<TABLE border=1 cellSpacing=0><TBODY><TR><TD>Pressure angle to pin center Bd:</TD><TD><CENTER>InBd=Tan(Bd)-Bd*pi/180 </CENTER></TD></TR></TBODY></TABLE>

For some reason my anwser doesn't come out the same as theirs?
Any suggestions?

Thanks!
-Zach

 

[zach454]

yes i named the ranges according the whatever that formula equalled. Like on the first formula:

Ad=arctan(tan(An)/cos(h))

I named the range that the anwser would belong to "Ad"


Your excel sheet came out with the same anwser as the website?

 

[Norie]

I didn't really get round to completing things.

There was quite a lot of stuff to go through, and I'm afraid I gave up after trying the obvious things like deg/rad.

Do you have a link to anything that explains these formulas?

 

[zach454]

I have been looking all over for something, and I can't find anything that I can understand, its just driving me nuts. Thank you for giving it a crack anyways, definitly apperciate it!

 

[MikeWx]

After looking in many places I found out that angle Bd has to be solved with a transcendental function (not 1960's Woodstock transcendental). The equation to solve it is:

Involute function of Bd (in radians) = Tan(Bd (in radians)) - Bd (in radians)

The only ways to do this in Excel are to either use Solver or to write a converging User Defined Function (UDF) in VBA.

The Solver solution to get the answer in Cell G20 is:
In Cell H20, Enter 0
In Cell I20, Enter =TAN(H20)-H20
Start Solver by selecting the Data Tab and selecting Solver under the Analysis subsection. If it doesn't appear, you need to add Solver from the Add Ins using the Excel Options in the upper left corner of Excel.

In Solver:
Set Target Cell is I20
Equal to is Value of 0.08987383698476035 which is the Involute function of Bd or Cell G19 in your spreadsheet
By Changing Cells is H20
Then press Solve. The answer in cell I20 is the Bd angle in radians.
To get the answer in degrees in cell G20, enter =i20*180/PI() in cell G20

Maybe someone else can come up with the UDF.

I learned a lot more about involute functions than I ever planned on learning.

 

[shg]

Nice work, Mike.

Here's a UDF

Function Inv(ByVal a As Double) As Double
    ' Returns the angle theta (in radians) such that a = tan(theta) - theta
    Const pi        As Double = 3.14159265358979
    Const D2R       As Double = pi / 180#
    Dim dMin        As Double
    Dim dMax        As Double
 
    If a <= 0 Then Exit Function
 
    dMin = D2R * 0
    Inv = D2R * 45
    dMax = D2R * 90
 
    Do While SearchIndex(dMin, Inv, dMax, Tan(Inv) - Inv > a)
    Loop
End Function
 
Private Function SearchIndex(ByRef dMin As Double, _
                            ByRef dMid As Double, _
                            ByRef dMax As Double, _
                            bTooBig As Boolean) As Boolean
    ' shg 2011
 
    ' Changes the search limits dMin and dMax and
    ' the next test value dMin based on their
    ' current values and the results of the last test.
    ' Returns False when there are no more values to test.
 
'    Debug.Print dMin, dMid, dMax, IIf(bTooBig, "Too big", "Too small")
 
    If dMid = dMin Or dMid = dMax Then Exit Function
 
    If bTooBig Then dMax = dMid Else dMin = dMid
    dMid = (dMax + dMin) / 2
    SearchIndex = True
End Function

E.g., =DEGREES(Inv(0.08987383698476035)) returns 35.0619...

 

[zach454]

Awesome Mike, Thanks! I only have 1 small problem, the solver won't work. Here is what I inputted:

When you click solver:

Set Objective: $I$20

To: Value Of: 0.08987 etc...

By Changing Variable Cells: $H$20

Select a solving Method: GRG Nonlinear

Is that correct? it keeps popping up saying it couldnt find a solution.

 

[zach454]

Wow, shg, that works great! Thanks a bunch guys, I really appreciate the help.

-Zach

 

[ZVI]

UDF OverBalls with the same algorithm as in link provided in post#1
Copy the code into VBA-Module and run testing subroutine Test_OverBalls

The code:

' UDF: Dimension over pins/balls/wire for external involute helical gear
'      or involute splines
' SYNTAX: OverBalls(Rng[, Index])
'
' ARGUMENTS: 
' Rng - range/array with 6 values:
'   1 - Number of teeth
'   2 - Diametral pitch
'   3 - Helix angle on pitch diameter
'   4 - Normal pressure angle on pitch diameter
'   5 - Normal arc tooth thickness on pitch diameter
'   6 - Ball/Pin/ Wire diameter
' Index - (Optional) If defined then index value is rerurned from output array.
'         Index range: 1 up to 13
'
' OUTPUT ARRAY:
' Index  Comment
'   1 - Transverse pressure angle
'   2 - Transverse arc tooth thickness
'   3 - Helix angle on base cylinger
'   4 - Transverse pin diameter
'   5 - Pitch diameter
'   6 - Base diameter
'   7 - Involute functin on pitch diameter
'   8 - Involute functin on ball tangent point
'   9 - Pressure angle to pin center
'  10 - Diameter of pin centers
'  11 - Dimension over pins
'  12 - Pressure angle at point of tangency
'  13 - Radius to point of tangency
'
' NOTE: If Index is omitted then 2-columns array is returned. 
'       Select 13 rows in 1 or 2 column(s), put formula and confirm by Ctrl-Shift-Enter
' Array formula example: =OverBalls(A2:A7)
' Single value formula:  =OverBalls(A2:A7,1) ... =OverBalls(A2:A7,13)
Function OverBalls(Rng, Optional Index = 0)
  
  Dim Inp, Ret(1 To 13, 1 To 2)
  Dim N_teeth#, D_Pitch#, Hel_Ang#, Nor_Pr#, Nor_Thick#, Ball_Dia#
  Dim tpa#, tatt#, habc#, tpd#, pd#, bd#, ifpd#, ifbtp#, papc#, dpc#, dop#, papt#, rpt#
  Dim a#, b#, tet#, i&
   
  Const j& = 100  ' <-- iterations
  Const pi# = 3.14159265358979
  Const D2R# = pi / 180, R2D# = 180 / pi, pi4# = pi / 4, pi8# = pi / 8
  
  ' Parse input range/array Rng to variables
  N_teeth = Rng(1)
  D_Pitch = Rng(2)
  Hel_Ang = Rng(3)
  Nor_Pr = Rng(4)
  Nor_Thick = Rng(5)
  Ball_Dia = Rng(6)
  
  tpa = Atn(Tan(D2R * Nor_Pr) / Cos(D2R * Hel_Ang)) * R2D
  Ret(1, 1) = tpa
  Ret(1, 2) = "Transverse pressure angle"
  
  tatt = Nor_Thick / Cos(D2R * Hel_Ang)
  Ret(2, 1) = tatt
  Ret(2, 2) = "Transverse arc tooth thickness"
  
  habc = Atn(Tan(D2R * Hel_Ang) * Cos(D2R * tpa)) * R2D
  Ret(3, 1) = habc
  Ret(3, 2) = "Helix angle on base cylinger"
  
  tpd = Ball_Dia / Cos(D2R * habc)
  Ret(4, 1) = tpd
  Ret(4, 2) = "Transverse pin diameter"
  
  pd = N_teeth / (D_Pitch * Cos(D2R * Hel_Ang))
  Ret(5, 1) = pd
  Ret(5, 2) = "Pitch diameter"
  
  bd = pd * Cos(D2R * tpa)
  Ret(6, 1) = bd
  Ret(6, 2) = "Base diameter"
  
  ifpd = Tan(D2R * tpa) - D2R * tpa
  Ret(7, 1) = ifpd
  Ret(7, 2) = "Involute function on pitch diameter"
  
  ifbtp = tatt / pd + tpd / bd + ifpd - pi / N_teeth
  Ret(8, 1) = ifbtp
  Ret(8, 2) = "Involute function on ball tangent point"

  a = pi4: b = pi8
  For i = 1 To j
    tet = Tan(a) - a
    If ifbtp < tet Then a = a - b Else a = a + b
    b = b / 2
  Next
  papc = R2D * a
  Ret(9, 1) = papc
  Ret(9, 2) = "Pressure angle to pin center"

  dpc = bd / Cos(D2R * papc)
  Ret(10, 1) = dpc
  Ret(10, 2) = "Diameter of pin centers"

  dop = Ball_Dia + dpc * Cos(0.5 * pi / N_teeth * (N_teeth Mod 2))
  Ret(11, 1) = dop
  Ret(11, 2) = "Dimension over pins"

  papt = Atn(Tan(D2R * papc) - Ball_Dia * Cos(D2R * habc) / bd) * R2D
  Ret(12, 1) = papt
  Ret(12, 2) = "Pressure angle at point of tangency"

  rpt = bd / 2 / Cos(D2R * papt)
  Ret(13, 1) = rpt
  Ret(13, 2) = "Radius to point of tangency"
  
  If Index > 0 And Index < 14 Then
    OverBalls = Ret(Index, 1)
  Else
    OverBalls = Ret
  End If
  
End Function

' Testing subroutine
Sub Test_OverBalls()
  
  Dim a, v, i
  
  ' Add new shhet for the testing
  With Sheets.Add(Sheets(1))
    
    .Range("A1") = "Input Values"
    .Range("A2") = 50
    .Range("A3") = 10
    .Range("A4") = 31.5
    .Range("A5") = 30
    .Range("A6") = 0.1544
    .Range("A7") = 0.1728
    
    .Range("B1") = "Comment"
    .Range("B2") = "Number of Teeth"
    .Range("B3") = "Diametral Pitch"
    .Range("B4") = "Helix Angle on Pitch Diameter"
    .Range("B5") = "Normal Pressure Angle on Pitch Diameter"
    .Range("B6") = "Normal Arc Tooth Thickness on Pitch Diameter"
    .Range("B7") = "Ball/Pin/Wire Diameter"
    
    With .Range("A9:B9")
      .MergeCells = True
      .HorizontalAlignment = xlCenter
      .Value = "A10:B22 Array Formula"
    End With
    .Range("A10:B22").FormulaArray = "=OverBalls(A2:A7)"
    
    .Range("D9") = "D10:D22 Array Formula"
    .Range("D10:D22").FormulaArray = "=OverBalls(A2:A7)"
    
    .Range("F9") = "Individual Formulas"
    .Range("F10:F22").Formula = "=OverBalls($A$2:$A$7,ROW()-9)"
    
    .Columns("A:F").AutoFit
  
  End With
    
  ' VBA testing of array returning
  Debug.Print "Array values:"
  a = OverBalls(Range("A2:A7"))
  For i = 1 To 13
    Debug.Print i, a(i, 1), a(i, 2)
  Next
  
  ' VBA testing of individual value returning
  Debug.Print "Some values:"
  Debug.Print 12, OverBalls(Range("A2:A7"), 12)
  Debug.Print 13, OverBalls(Range("A2:A7"), 13)

End Sub

Generated testing sheet:

Excel Workbook
	A	B	C	D	E	F
1	Input Values	Comment
2	50	Number of Teeth
3	10	Diametral Pitch
4	31.5	Helix Angle on Pitch Diameter
5	30	Normal Pressure Angle on Pitch Diameter
6	0.1544	Normal Arc Tooth Thickness on Pitch Diameter
7	0.1728	Ball/Pin/Wire Diameter
8
9	A10:B22 Array Formula			D10:D22 Array Formula		Individual Formulas
10	34.10320123	Transverse pressure angle		34.10320123		34.10320123
11	0.181084596	Transverse arc tooth thickness		0.181084596		0.181084596
12	26.90400405	Helix angle on base cylinger		26.90400405		26.90400405
13	0.193772826	Transverse pin diameter		0.193772826		0.193772826
14	5.864138483	Pitch diameter		5.864138483		5.864138483
15	4.855676779	Base diameter		4.855676779		4.855676779
16	0.081919239	Involute function on pitch diameter		0.081919239		0.081919239
17	0.089873837	Involute function on ball tangent point		0.089873837		0.089873837
18	35.06197302	Pressure angle to pin center		35.06197302		35.06197302
19	5.932183136	Diameter of pin centers		5.932183136		5.932183136
20	6.104983136	Dimension over pins		6.104983136		6.104983136
21	33.82545607	Pressure angle at point of tangency		33.82545607		33.82545607
22	2.922510637	Radius to point of tangency		2.922510637		2.922510637
Sheet

 

[MikeWx]

It looks like you won't need to use Solver with the UDF solutions that have been presented. The Excel 2007 version of Solver didn't have a choice for Select a solving Method, and didn't change any of the options.

Thanks for your feedback on the results.

Mike