Raytracing in Excel
Having seen the example of someone actually doing this in real life (seriously! s0lly is a genius), I was curious about how the math and setup may have looked. Excited that we now have an entity that will answer and not tire of these inane questions, I decided to ask (my questions and follow-ups in bold):
Hi ChatGPT, can you show how to setup excel spreadsheets to demonstrate raytracing?
Raytracing is a complex graphics rendering technique used in computer graphics, typically used in 3D software or game engines. Although Excel isn't the ideal platform for creating or demonstrating raytracing, you can still create a simplified, conceptual representation of raytracing using a spreadsheet. Here's a step-by-step guide to setting up a basic raytracing demonstration in Excel:
1. Open a new Excel workbook.
2. Rename the first sheet to "Raytracing".
3. In the top row, create headers to represent different elements of raytracing:
A1: Ray
B1: Origin (x)
C1: Origin (y)
D1: Direction (x)
E1: Direction (y)
F1: Intersection
G1: Object
H1: Distance
4. List rays by giving them an index in column A, starting from A2 (e.g., Ray 1, Ray 2, Ray 3).
5. Define the origin of each ray by entering the x and y coordinates in columns B and C, respectively.
6. Define the direction of each ray by entering the x and y components of the direction vector in columns D and E, respectively. You can normalize these vectors to simplify calculations.
7. In column F, use a formula to determine if a ray intersects an object. For a simple 2D scene, you can use basic geometric shapes like circles or rectangles. You'll need to create separate sheets for each object and list their properties (position, size, etc.).
8. In column G, indicate which object the ray intersects (if any).
9. In column H, calculate the distance between the ray's origin and the intersection point.
Note that this is a highly simplified and conceptual representation of raytracing, and it doesn't take into account aspects like shading, reflections, or refractions. Excel isn't designed for complex graphics rendering, so for a more accurate and realistic raytracing simulation, you should consider using dedicated software like Blender or a game engine like Unity or Unreal Engine.
Could you demonstrate an example of a sphere lit with a point light source with an orthographic view?
Creating a true 3D representation of a sphere lit by a point light source with an orthographic view in Excel is beyond the capabilities of a spreadsheet application. However, I can provide a conceptual example to help you visualize how the lighting works in this scenario.
1. Create a new sheet and name it "SphereLighting".
2. Create headers for different elements:
A1: Point
B1: Point (x)
C1: Point (y)
D1: Point (z)
E1: Light Intensity
F1: Distance to Light
G1: Surface Normal (x)
H1: Surface Normal (y)
I1: Surface Normal (z)
J1: Light Direction (x)
K1: Light Direction (y)
L1: Light Direction (z)
M1: Dot Product
N1: Shaded Intensity
3. Define the sphere's center coordinates and radius.
4. Define the point light source's position and intensity.
5. Create a series of points on the sphere's surface. You can do this by creating a grid of points and then removing the points that are not on the sphere's surface.
6. For each point, calculate the surface normal vector (G1, H1, I1) by subtracting the sphere's center coordinates from the point coordinates.
7. For each point, calculate the light direction vector (J1, K1, L1) by subtracting the point coordinates from the light source's position.
8. Normalize both the surface normal and light direction vectors.
9. Calculate the dot product (M1) of the normalized surface normal and light direction vectors. If the dot product is negative, set it to 0.
10. Multiply the dot product by the light source's intensity to get the shaded intensity (N1) at each point.
This conceptual example provides a simplified representation of the lighting on the sphere's surface but is not an accurate 3D representation of the scene. To create a true 3D model of a sphere lit by a point light source with an orthographic view, consider using dedicated 3D software like Blender, Maya, or 3ds Max.
Could you demonstrate the excel formula for: For each point, calculate the surface normal vector (G1, H1, I1) by subtracting the sphere's center coordinates from the point coordinates.
Certainly! Let's assume that the sphere's center coordinates are defined in specific cells as follows:
X1: Sphere Center X
Y1: Sphere Center Y
Z1: Sphere Center Z
And the point coordinates are in columns B, C, and D, as mentioned earlier:
B2: Point X
C2: Point Y
D2: Point Z
You can calculate the surface normal vector components for each point using the following Excel formulas:
G2 (Surface Normal X): =B2 - $X$1
H2 (Surface Normal Y): =C2 - $Y$1
I2 (Surface Normal Z): =D2 - $Z$1
These formulas subtract the sphere's center coordinates from the point coordinates, resulting in the surface normal vector components. The dollar signs before the row numbers in $X$1, $Y$1, and $Z$1 make them absolute references, so the formulas will always refer to the sphere's center coordinates when you copy the formulas down to other rows for additional points on the sphere's surface.
Thanks, could you also demonstrate a formula for: For each point, calculate the light direction vector (J1, K1, L1) by subtracting the point coordinates from the light source's position.
Sure! Let's assume that the light source's position is defined in specific cells as follows:
X2: Light Source X
Y2: Light Source Y
Z2: Light Source Z
And the point coordinates are in columns B, C, and D, as mentioned earlier:
B2: Point X
C2: Point Y
D2: Point Z
You can calculate the light direction vector components for each point using the following Excel formulas:
J2 (Light Direction X): =$X$2 - B2
K2 (Light Direction Y): =$Y$2 - C2
L2 (Light Direction Z): =$Z$2 - D2
These formulas subtract the point coordinates from the light source's position, resulting in the light direction vector components. The dollar signs before the row and column in $X$2, $Y$2, and $Z$2 make them absolute references, so the formulas will always refer to the light source's position when you copy the formulas down to other rows for additional points on the sphere's surface.
Great, and lets show the formulas for the remaining:
Normalize both the surface normal and light direction vectors.
Calculate the dot product (M1) of the normalized surface normal and light direction vectors. If the dot product is negative, set it to 0.
Multiply the dot product by the light source's intensity to get the shaded intensity (N1) at each point.
Sure! Let's continue with the surface normal vector components in columns G, H, and I, and the light direction vector components in columns J, K, and L.
1. Normalize the surface normal vector:
First, calculate the length of the surface normal vector in column O:
O2: =SQRT(G2^2 + H2^2 + I2^2)
Next, divide each component of the surface normal vector by its length:
P2 (Normalized Surface Normal X): =G2/O2
Q2 (Normalized Surface Normal Y): =H2/O2
R2 (Normalized Surface Normal Z): =I2/O2
2. Normalize the light direction vector:
First, calculate the length of the light direction vector in column S:
S2: =SQRT(J2^2 + K2^2 + L2^2)
Next, divide each component of the light direction vector by its length:
T2 (Normalized Light Direction X): =J2/S2
U2 (Normalized Light Direction Y): =K2/S2
V2 (Normalized Light Direction Z): =L2/S2
3. Calculate the dot product (M1) of the normalized surface normal and light direction vectors:
M2: =P2*T2 + Q2*U2 + R2*V2
4. If the dot product is negative, set it to 0:
M2 (updated): =MAX(0, M2)
5. Multiply the dot product by the light source's intensity to get the shaded intensity (N1) at each point. Assuming the light intensity is stored in cell X3:
N2: =M2 * $X$3
Now you have the shaded intensity at each point on the sphere's surface. Remember to copy these formulas down to other rows for additional points on the sphere's surface.
Great. Can you recap using only formulas?
Certainly! Here's a recap of the Excel formulas:
Assuming:
- Sphere's center coordinates: $X$1, $Y$1, $Z$1
- Point coordinates: B2, C2, D2
- Light source's position: $X$2, $Y$2, $Z$2
- Light source's intensity: $X$3
1. Surface normal vector components:
G2: =B2 - $X$1
H2: =C2 - $Y$1
I2: =D2 - $Z$1
2. Light direction vector components:
J2: =$X$2 - B2
K2: =$Y$2 - C2
L2: =$Z$2 - D2
3. Normalize surface normal vector:
O2: =SQRT(G2^2 + H2^2 + I2^2)
P2: =G2/O2
Q2: =H2/O2
R2: =I2/O2
4. Normalize light direction vector:
S2: =SQRT(J2^2 + K2^2 + L2^2)
T2: =J2/S2
U2: =K2/S2
V2: =L2/S2
5. Dot product:
M2: =MAX(0, P2*T2 + Q2*U2 + R2*V2)
6. Shaded intensity:
N2: =M2 * $X$3
Make sure to copy these formulas down to other rows for additional points on the sphere's surface.
Will do. What would it look like to setup a sheet to preview the rendered state by assigning each cell to a pixel and linking the calculations to other sheets as outlined above?
To create a preview sheet with a rendered state by assigning each cell as a pixel and linking calculations to other sheets, follow these steps:
Create a new sheet and name it "Preview".
Adjust the row height and column width to make the cells look like square pixels. You can do this by right-clicking on the row or column headers, selecting "Row Height" or "Column Width," and entering a suitable value (e.g., 5).
Set up a grid representing the sphere's surface by assigning coordinates (x, y) to each cell. For example, you can use the top-left cell (A1) as the origin (0, 0) and increase the coordinates for each subsequent cell.
In the "SphereLighting" sheet, create a new column called "Coordinates" (e.g., column O) and combine the point x and y coordinates as text in the following format: "x,y". Use this formula in O2 and copy it down for all points:
O2: =B2 & "," & C2
On the "Preview" sheet, for each cell, use a formula to look up the shaded intensity (from the "SphereLighting" sheet) corresponding to the cell's coordinates. Assuming the rendered state is within a reasonable range, you can use the following formula in A1 and copy it to all cells within the grid:
A1: =IFERROR(VLOOKUP(TEXT(ROW(A1)-1,"0") & "," & TEXT(COLUMN(A1)-1,"0"), 'SphereLighting'!O:N, 6, FALSE), 0)
This formula searches for the cell's coordinates in the "SphereLighting" sheet and retrieves the corresponding shaded intensity. If there's no match, it returns 0.
Apply conditional formatting to the cells in the "Preview" sheet to represent the intensity levels with a color scale. To do this, select all cells in the grid, go to the "Home" tab, click on "Conditional Formatting," choose "Color Scales," and pick a suitable color scale (e.g., black-to-white for grayscale or a more colorful gradient).
Now the "Preview" sheet should display a rendered state of the sphere with each cell representing a pixel, based on the calculations from the "SphereLighting" sheet. Note that this is a simplified, 2D representation and not an accurate 3D rendering, but it should give you a visual indication of how the point light source affects the shading on the sphere's surface.
I’d like to test this and see what it makes, but a project for another day hopefully!
Written by: Peter Kagstrom, ChatGPT
Thumbnail image generated by Stable Diffusion with prompt:
An abstract minimalist representation of a primitive computer being used to demonstrate AV processing and simulation technologies far ahead of its time