# 3D-2D Perspective Mapping in Excel – part #1

In computer graphics we often need to be able to display a three-dimensional image in two dimensions and preserve the perspective appearance. If we walk on a straight road, it appears that the road narrows with the distance. This is the perspective effect and it is a result of mapping a three-dimensional image on a two-dimensional surface (i.e. a computer monitor, a screen, or the retina of the eye).

This tutorial is not about stereoscopic view, which refers to a technique for enhancing the illusion of depth in an image by presenting two offset images separately to the left and right eye of the viewer.  [sociallocker] [/sociallocker]

## 3D Perspective Conversion in Excel – Part #1

– by George Lungu

In computer graphics we often need to be able to display a three-dimensional
image in two dimensions and preserve the perspective appearance. If we walk on a

This is the perspective effect and it is a result of mapping a three-dimensional image on a two-
dimensional surface (i.e. a computer monitor, a screen, or the retina of the eye).

This tutorial is not about stereoscopic view, which refers to a technique for
enhancing the illusion of depth in an image by presenting two offset images separately
to the left and right eye of the viewer.

<www.excelunusual.com> 1

#### The setup:

– Let’s assume we are watching point “P” of a 3D scene through a piece of glass screen

(Object)
O A x
E Glass
Screen
Eye

<www.excelunusual.com> 2

– We define the “object system of coordinate” which is a 3D system of coordinates (x,y,z) in which point P (object) is situated

– We also define a 2D system of coordinates (u,v) on the glass screen (screen system) z (Target)

– Besides the glass screen let’s consider two more planes involved which are perpendicular on each other and perpendicular on the glass screen

– There is the horizontal plane which contains the O’u axis and Ox axis

– There is also the vertical plane which P’ contains the O’v axis and Oz axis

– Point P in the object space is defined by three coordinates: (x,y,z)

– Goal: Using a gun placed in point E u to precisely shoot at point P, calculate the (u,v) coordinates of the bullet
hole through the screen glass knowing (x,y,z) and the length of two more segments: ES (eye to screen)
and SO (screen to the origin of the Screen object system of coordinates) Vertical

Bullet Hole
Gun
Glass
Horizontal
Plane
eye and screen

<www.excelunusual.com> 3

### Let’s now look at the horizontal plane:

(Object)
y, B, P’
Horizontal O A
x, Plane

– The following two triangles are similar triangles: EO’A’ and EBP’, A’A P’ therefore we can write the following proportionality equality:
ES SA’ x ES
And from here we can calculate “u”:

We can do the same type of analysis in the vertical plane:
C P’’
– The following two triangles are similar triangles: ESC’ and EBP’’, therefore we can write the following proportionality equality:

And from here we can calculate “v”: EB BP”

Where:
xES
– ES is the distance between

### In conclusion we have the following conversion equations:

ES =SO* y

Where:
-ES is the distance between eye and screen
-SO is the distance between the screen and the origin of the object system of coordinates

The object has to be in front of the observer therefore EB (ES+SO+y>0) must be positive at
all times to prevent artifacts from being displayed on the screen.
While displaying multi-point shapes, the perspective conversion program has to be written as to
eliminate any shape that has even one vertex which does not satisfy the previous condition.

<www.excelunusual.com> 5

### Let’s create a sample worksheet to verify the concepts covered here:

-Range (B12:D22) contains the 3-dimensional data for building the following pyramid:

-The distance eye-screen is constant, set at 3
-The distance screen-origin can be adjusted within the range [-3, 7] using a spinner button and an associated macro

– Range (F12:G22) contains the 3D to 2D conversion formulas described earlier in this presentation:

– F12: “=B12*B\$3/(B\$3+B\$5+C12)”
– G12: “=D12*B\$3/(B\$3+B\$5+C12)”
– Copy down F12:G12 to F22:G22

– Adjust the screen-origin distance and see how the pyramid changes position
– See what happens when the screen-origin distance gets too small
– Also verify how decreasing the eye-screen distance increases the perspective effect

by George Lungu <www.excelunusual.com>