Ray Trace Suggestions

Suggestions for a Ray Tracing Spreadsheet

There are a number of 'series' which make up the spreadsheet

A line to represent the object, one to represent the image, one to represent the lens
One series of lines to represent each ray
(Each ray will occupy two columns, one for height and one for angle)

Let's start with one lens, one object, one ray and one image (the figure is a little more advanced)

The object will be at x=0, an initial height of 1
The first ray will start out with an angle of 0
The lens will start out at x=20 (later, we may adjust its position with a slider)
We will put labels in A5..A8 for 'object x', 'lens x', 'final x' and 'lens f'

'final x' denotes the x limit of our graph
'lens f' is the lens focal length

In B5..B8 we will define the appropriate cells as 'object_x', etc.
Columns C and D will be devoted to the ray (C for its height y, D for its angle)
In column B5..B8, enter '0', '20', and '100' and '10'.

100 is the far extent of the graph
10 is the focal length of the lens

In C3 type 'Ray 1', in C4 type 'y', in D4 type 'angle'
In C5 enter '1', and in D5 enter '0' (the initial height and angle of ray 1)
C6 and D6 are the height and angle leaving the lens
In C6 you would enter '=C5+D5*(lens_x-object_x)'
In D6 you would enter '=D5-C5/lens_f'
To take the ray to the final x you would fill C7 and D7:
In C7, it would be '=C6+D6*(final_x-lens_x)'. In D7 it would be '=D6'
Now you want to plot the ray, so you drag over C5..D7 and bring up an x-y graph of this region.
Next, you want a series to plot the image so put the image x and y properties in B12..C13 as shown.
You add the series for the image by going to a data point on the graph and right-clicking
Then select Source Data
You add in the series so the x values are B12..B13 and the y values are C12..C13
(I copy the x-data for another series, then paste it in for the new series both x and y, then correct the values)
Next you can add a series to show the lens. I put its values in B15..C16, letting its top be +5 and its bottom be -5.
For the series to show the image you must calculate its position and height, then put them in.
To introduce a second ray, you use columns E and F, starting the second ray off with a small angle like 0.1 and of couse a height of 1. After the first line for a new ray, you can copy and paste from the x,y values of the old ray.
The figure shows a more advanced sheet, using a slider to adjust the focal length of the lens.

Enhancements like sliders are desirable, because one can see the image move around as the lens position is varied, or the lens focal length is varied.