Recently I happened to read of Carlyle’s Circle, which is a circle that has two intersection points with a parabola on the X axis. See the Wikipedia article for more details:
https://en.wikipedia.org/wiki/Carlyle_circle
This post looks at the options for plotting this circle in Excel, using both the Excel built-in xy graphs and Python Matplotlib graphs with the pyxll add-in. The new Python code used has been added to the py_SciPy code, with examples added to the py_EvalU spreadsheet, both of which can be downloaded from:
See Scipy Functions with Excel and pyxll 5 – Evaluate with Units for more details of the py_EvalU spreadsheet.
Using the py_EvalU spreadsheet, the functions for a parabola and the associated Carlyle Circle can be entered as text, which can be evaluated and listed over a specified range, using the py_Eval function. These two lines can then be plotted on an Excel xy graph:
As an alternative, Matplotlib can be used with pyxll to generate the plot data and generate an xy graph. This has the advantages:
- The generated data does not need to be written to the spreadsheet.
- It is easier to set axis limits, and to set the axis scales equal, so that the circle plots as a circle and not an ellipse.
- More and easier options for adding text and titles, etc.
The py_PlotFunc function will evaluate the parabola over a specified range of x, and plot the result:
py_PlotCirc plots a circle with a specified centre and radius:
These two functions are called by py_PlotCarlyle, which has the parameters of the parabola as input, and calculates the centre and radius of the Carlyle Circe, then plots both, together with the diameter of the circle, and the coordinates of the intersection of the circle with the x axis, which are also the intersection points with the parabola:
The code for the py_PlotCarlyle function is shown below. All the other code is freely available in the download file.
@xl_func(category = "Eval-Units")
@xl_arg('Quadabc', 'numpy_array<float>', ndim=1)
@xl_arg("EqualAx", 'bool')
@xl_arg("DecPlace", 'int')
def py_PlotCarlyle(Quadabc, EqualAx = True, DecPlace = 3):
a, b, c = Quadabc[:]
if b**2 - 4*a*c < 0:
return 'No real solutions'
xdiff = (b**2 - 4*a*c)**0.5
x1 = (-b - xdiff)/(2*a)
x2 = (-b + xdiff)/(2*a)
roots = [x1, x2]
xlabels = ['x = ' + str(round(num,DecPlace)) for num in roots]
if b < 0:
btxt = str(b)
else:
btxt = "+ " + str(b)
if c < 0:
ctxt = str(c)
else:
ctxt = "+ " + str(c)
head = "Carlyle's Circle for y = " + str(a) + "x^2 " + btxt + "x " + ctxt
n = 40
s = -b/a
p = c/a
Xcen = s/2
Ycen = 1 + (p-1)/2
r = ((s**2 + (p-1)**2)**0.5)/2
xmin = (Xcen - r)
xmax = (Xcen + r)
xquad = np.linspace(xmin, xmax, n)
yquad = np.zeros(n)
for i in range(0, n):
x = xquad[i]
yquad[i] = a*x**2+b*x+c
angvals = np.linspace(0, np.pi*2, n*2)
sinangs = np.sin(angvals)
cosangs = np.cos(angvals)
xcirc = np.zeros(n*2)
ycirc = np.zeros(n*2)
xcirc[:] = cosangs[:]*r +Xcen
ycirc[:] = sinangs[:]*r +Ycen
xdiam = [0, s]
ydiam = [1, p]
labels = [' 0, 1', ' -b/a, c/a']
fig, ax = plt.subplots()
ax.plot(xquad, yquad, 'r', xcirc, ycirc, 'b', xdiam, ydiam, 'g')
plt.grid(True)
plt.title(head)
if EqualAx: plt.axis('equal')
for i in range(2):
plt.text(xdiam[i], ydiam[i], labels[i], fontsize = 10, ha = 'left', va = 'top')
plt.text(roots[0], 0, xlabels[0], fontsize = 10, ha = 'left', va = 'bottom')
plt.text(roots[1], 0, xlabels[1], fontsize = 10, ha = 'left', va = 'top')
# Optionally customize the grid
plt.grid(color='gray', linestyle='--', linewidth=0.5)
plot(fig)
return ' Function roots are ' + xlabels[0] + ' and ' + xlabels[1]
Newton Excel Bach, not (just) an Excel Blog 