A lot has been written and discussed about how to calculate the inverse of involute angle in technological magazines and publications in the past with the help of a simple Computer Program. There are three ways with which inverse of involute angles can be found in Microsoft Excel without Programming, including:
1. Excel Circular Referencing
2. Excel Goal Seek Add-in
3. Excel Solver Add-in
In this article, we discuss how to calculate the inverse of involute angles without any programming by Excel Circular Referencing & also a simple VB Program to show how excel performs this task internally.
Microsoft Excel Circular Referencing Technique
In Microsoft Excel when a formula refers back to its own cell, either directly or indirectly, it creates a circular reference. By default, iterative calculations are turned off in Microsoft Office Excel. You can enable iterative calculations as described below.
• Click the Microsoft Office Button, click Excel Options, and then click the Formulas category.
• In the Calculation options section, select the Enable iterative calculation check box. See Figure 1.
• Number of iterations can be set in the maximum iterations box. The higher the value the more time Excel will take to calculate the worksheet.
• The maximum change represents the difference between two successive results, while performing the above number of iterations. The smaller the number, the more accurate the result.
• Once the difference between two successive results is less than maximum change value the iterations shall stop and Excel will display the latest result obtained. So the set maximum iteration will not be performed.
• If the difference between two successive results is never less than or equal to the value entered in maximum change, Excel shall carry out the iterations for as many numbers of times as entered in the maximum iterations input box and then stop displaying whatever value is calculated after those iterations. The value obtained may not be correct.
Equation for Involute
• Inv Φ = tan Φ – Φ, where “Φ” is in radians.
There are two ways with which we can modify this equation to be used as Circular Reference in Microsoft Excel.
i.e. Φ = arc tan (Inv Φ + Φ) OR tan-1 ( Inv Φ + Φ) ( 1 )
i.e. ø = tan Φ – Inv Φ ( 2 )
How we construct our equation for circular reference decides the accuracy of results obtained. It is not necessary that we get a correct result always by using circular Reference. In case of involute angle equation (1) shall give us correct results based on maximum iterations and maximum change selected.
• In Figure 1, For calculating Inverse of Involute Angle we shall use a value of maximum iterations as 100, which is enough to find the inverse of the involute angle up to 10 digits precisely after decimal point.
• And we shall use a value for Maximum Change as 0.000001, which represents precision up to 6 significant digits after decimal point.
Figure 2 shows how to enter the formula in Excel for both the above equations so as to get the correct results after turning on iterative calculations in Excel. Also shown is the re-calculation formula of involute ‘f’ in the output section.
Equation (1) returns correct results in a fraction of a second as if a simple formula is entered into a cell in Excel. You can try various input values for maximum iterations & maximum change and check the results. You can find the value of involute angle accurate up to 15 decimal places in a fraction of a second.
Equation (2) does not yield proper results even if maximum number of iterations is increased to 32767 (maximum allowed) with the precision set to 6 significant digits. With precision set to 3 significant digits you might get an answer but that is not as accurate as we want it to be. This is just to show that not all equations for circular references yields correct results.
How Does Excel Do It?
Figure 4 represents a visual basic code for calculating the involute angle, which is very similar to how Excel performs calculations wherever circular references are used in the worksheet. Readers can use this code in case they don’t want to use circular reference in Excel. The VB Code is shown for Equation (1).
In the above VB Code we set the value of Angle 1 = 0 before starting our iterations whereas when Excel does it, it uses the last calculated value in the cell as a starting point for its iterations rather than 0.
Equation for Sevolute
Sevolute is used in determining the generated full fillet at the root of the gear. A full tip radius also helps in reducing the stress concentration at the corner of the internal gears.
Sevolute(ε) = sec(ε) – inv(ε) = [1/cos(ε)] – Tan(ε) + (ε)
There are three ways with which we can modify this equation to be used as circular reference in Microsoft Excel.
i.e. ε = Sevolute(ε) – 1/Cos(ε) + Tan(ε) ( 3 )
i.e. ε = arc tan [1/Cos(ε) – Sevolute(ε) + ε] ( 4 )
i.e. ε = arc cos [1/(Sevolute(ε) + Tan(ε) – ε)] ( 5 )
In Figure 1, for calculating Inverse of sevolute angle we shall use a value of maximum iterations as 100 and value for maximum change as 0.000001.
Equation (3) returns correct results in a fraction of a second. But you need to enter values of sevolute ε in the input in between 1.0035 to 2.14. This shall re-calculate value of ε in between 5 to 175 degrees. For all practical purposes we don’t need to calculate value of ε more than 175 degrees. In case you need to calculate the value of ε below 5 degrees with good precision, then you can use equation (5) or you need to increase the maximum iterations from Figure 1 and re-check the results.
Equation (4) does yield correct results considering the re-calculation of sevolute ε but the result is negative value for ε and we are looking for a positive value. For our calculation, negative values are incorrect as Involute does not exist for negative pressure angle.
Equation (5) returns correct results instantly, but you need to enter values of sevolute ε in the input in between 1.0 to 1.35 for maximum iterations equal to 100 and maximum change equal to 0.000001. This shall re-calculate the value of ε in between 0 to 65 degrees. For all gear applications we don’t need to calculate value of ε more than 65 degrees. To calculate the value of ε accurate up to 85 degrees you need to increase the maximum iternations from Figure 1. To 10000 and re-check the results. You can enter up to 32767 value in maximum iterations input box.
Equation (3) can be used for calculation of fillet radius of internal gears as we never enter the value of sevolute ε less than 1.0035 in this case and equation (5) can be used for the calculation of fillet radius for external gears as it can give correct results starting from 0 degree to 65 degrees instantly.
Note: Whenever you use a higher value for maximum iterations and Excel is taking too much time in calculating the result you can press “ESC” and the Excel program will stop performing the iterations. The results displayed in that case may not be correct.
Readers are advised to read the “Remove or allow Circular Reference” article in Excel Help for a better understanding and in case they wish to use circular reference for their own various equations.
One should not use too many circular references in Excel otherwise Excel might go in an endless loop. Pressing “ESC” might end that loop but that is not advisable.
Other Gear Calculations
As discussed wherever there is an equation whose solution can be found out only by iterative means you can try to use Excel circular referencing and Excel will do the rest without you getting involved in programming.
A lot of other gear calculations, such as tip chamfers, finding minimum pin diameter that fits a particular external or internal gear, protuberance can be calculated using Excel circular referencing but one should know how to form the correct equation for it and should also know the limits of input for which the equation yields correct results.
References
[1] Involute splines and inspection ANSI B92.1-1970, Society of Automotive Engineers.
[2] Excel 2007 Help.
[3] Earle Buckingham – Manual of Gear Design – Part 2.
