Spreadsheet Building
■ Table A shows the spreadsheet we constructed to calculate the required co-ordinate values. It is quite straightforward to reproduce, as will be outlined here.
Input values are shown highlighted in green boxes. Calculated ‘useful-information’ values are highlighted in the pale gold-coloured boxes and the ‘A’ and ‘B’ co-ordinate values for the cutting operations are then provided in the relevant columns (see Figure 2 for the definitions of the axes). The pale grey text contains interim calculated values, which are also useful in understanding the operation of the technique and its results – these do not need to be referred to for the cutting operations, however.
The spreadsheet was constructed in Microsoft Excel 2019, however it should be possible to produce something similar in any version of any spreadsheet package, although perhaps without the ‘suppression’ features which only show the required -90 to +90 range of results, no matter what ‘step’ precision is desired. These will be highlighted where relevant, so that they can be omitted if not supported in your package.
Cells A4 to C11 (inclusive) are simply input variables and the text etc. may be copied directly from Table 1. The formula for cell B13 is:
Cell B13: =$B$10/$B$8
The spreadsheet implements the more accurate method of equalising the angles within the circle at which the cutter will finish each cut and hence the angular steps need to be derived from the required arc size/ length and the desired number of steps – this is calculated in cell B13.
Note that the ‘$’ symbols ensure that if the cell is copied and pasted elsewhere then the same source row and column (in other words the same cell) will be used in the new location (so B10 and B8 will always be used) – the use of the $ symbol is not strictly necessary here, but does no harm.
We will discuss the ‘Maximum Error’ calculation below and hence can move on to cell G4. This calculates the radius of the arc through which the cutter would, ideally, move if it were driven by a CNC algorithm, rotating vice or other similar mechanism. We will move our cutter to an arbitrary number of individual points, which sit on this arc. The arc’s radius is simply the difference between the cutter radius and the desired hole radius:
Cell G4: =B6-B4
Cell E8 is set to -90, our starting angle:
Cell E8: =-90
Cell E9 then calculates the next angular step in our arc:
Cell E9: =IF(E8<90, E8+$B$13,””)
This uses an ‘IF’ statement to suppress any entries which go beyond +90 degrees. The contents of cell E9 can be copied and pasted down column E to accommodate the
maximum resolution you are ever likely to need (say 25 entries?). If you do this correctly the ‘E8’ entry from cell E9 will be automatically replaced by the relevant cell reference. For example, cell E12 should read:
Cell E12: =IF(E11<90, E11+$B$13,””)
Similarly, the ‘B-coordinate’ column should begin with:
Cell F8: =IF(ISNUMBER(E8), $G$4*SIN(RADIANS(E8)),””)
Again, the ‘IF’ and ‘ISNUMBER’ functions are used simply to suppress unwanted values (beyond the range of interest), the part that really matters is the “$G$4*SIN(RADIANS(E8))”, if your spreadsheet package doesn’t support such more advanced functions.
As above, copy and paste cell F8 for the same number of rows (say, 25) as before.
Cell G8 calculates the ‘A’ ordinate from the corresponding “B” ordinate:
Cell G8: =IF(ISNUMBER(F8),SQRT($G$4^2ABS(F8)^2),””)
“Working
out a formula for the size of the cusps and thereby the degree by which the cut circle deviates from a ‘perfect’ circle, is a little tricky...”
The ‘SQRT($G$4^2-ABS(F8)^2)’ section is the crucial part here, with the ‘IF’ and ‘ISNUMBER’ functions again acting to suppress unwanted entries (those beyond the required angular range).
If you only want to calculate the coordinate values and are not interested in the maximum error which will result, then you now have all you need. If you want to add (and understand) the maximum error calculation, then read on...
Calculating the error
Working out a formula for the size of the cusps and thereby the degree by which the cut circle deviates from a ‘perfect’ circle, is a little tricky. For those who are interested, I will outline the basic steps below, however it is not necessary to understand the calculation in order to make use of the cells added to the spreadsheet to undertake this task.
Figure A shows two adjacent final cutter positions, as would be calculated by our spreadsheet. It is possible to work straight from this diagram, in order to calculate the maximum error (shown by the red double-headed arrow) however it makes the algebra unnecessarily complicated! It is much simpler to rotate the axes such that the location of the maximum error, and hence the intersections of the circles, appear on a new ‘B’ axis. This situation is shown in Figure B.
Figure B makes the algebra a lot simpler: the equations of the two circles are now identical, with the exception of their horizontal coordinates (-P and +P respectively), and even these are equal in magnitude. Based upon the above, the steps are:
1) Rotate the axes 2): Derive an expression for either circle using the standard circle formula [(x-a)2 + (y-b)2 = R2, where
‘a’ and ‘b’ are the x and y coordinates of the centre of the circle and R is its radius]
3) Substitute into this expression ‘New A’ = 0 since the intersection between the two must occur on the ‘New B’ axis (as shown in Figure 9). 4) Find the roots of the resulting quadratic equation using the standard quadratic formula (remember this from O-level or GCSE maths?)
Cell B17: =(2*ABS($I$8)+SQRT(4*$I$8^24*($I$8^2+$K$8^2-$B$4^2)))/2
Cell B18: =(2*ABS($I$8)-SQRT(4*$I$8^24*($I$8^2+$K$8^2-$B$4^2)))/2
5) Select the correct root (in other words the largest one). This is the root shown in cell B17.
6) Subtract this root from the desired hole radius (cell B6)
Cell B19: =B6-B17
This (cell B19) is then the maximum error, in other words the deviation from a perfect circle of the desired radius.