Резюме. The formal representation of mathematical relations in spreadsheets is different from the usual algebraic representation and computer programs doing mathematics. We will demonstrate how this alternative representation can help understanding mathematical concepts in a new way that can be very helpful for learners not yet fluent in the “algebraic language” of mathematics.

Ключови думи: spreadsheet tools, spreadsheet, mathematics, programming

1. Preliminary remarks

The “standard” way of expressing mathematical concepts is algebraic notation. Most programming also uses a similar idiom to express mathematical computations. This representation uses variables (usually represented by letters, sometimes by words) and operators. Spreadsheets programs work differently. It is not necessary to use names in spreadsheets, references to cells are used instead.

A somewhat simplified description of spreadsheets is that they are two-dimensional tables, and each cell of the table can contain either a value or an expression (formula) computing a value from values in other cells (which by themselves also might be computed by formulas).

Spatial arrangement is very important to understand structural relationships in spreadsheets as the following example hopefully will demonstrate.

2. An introductory example

Which table is characterized by the following description?

– The cell in the upper left corner contains 1.

– The rest of the top row contains 0 everywhere.

– Each cell in the leftmost column (except the top one) contains the same value as the cell directly above, namely 1.

– All other cells contain the sum of the value in the cell above and the cell above and to the left.

The structure representing the relations between the values in this table can be depicted in the following arrow diagram:

Modern spreadsheet programs like Microsoft Excel or LibreOffice or OpenOffice allow creating this kind of visualization of dependencies as menu option.

In Excel, the menu item Trace Precedents will produce this representation.

In classical algebraic notation the table we are discussing can be written as an argument of two nonnegative integer arguments in the following way (\(n\) is the row index and \(k\) is the column index):

\[ B(n, k)= \begin{cases}1 & \text { for } n=0 \text { and } k=0 \\ 0 & \text { for } n=0 \text { and } k \gt 0 \\ B(n-1, k) & \text { for } n \gt 0 \text { and } k=0 \\ B(n-1, k-1)+B(n-1, k) & \text { far } n \gt 0 \text { and } k \gt 0\end{cases} \]

These 3 representations (verbal, table, algebraic formula) are equivalent. The classical mathematical (algebraic) representations, however, is harder to understand than the other two for most nonmathematicians.

Recursion is an essential constituent of this structure. The graphical representation very clearly shows why the recursive definition - which seems to me self-referential is not inconsistent. All the formulas below the top row refer to “the row above”, and the top row does not refer to anything else. Therefore, tracing any definition will stop after a finite number of steps.

The graphical representation allows additional insights.

The first visualization of the table answers the question “where from do the cells take their input”? We also can ask the question “where to do the contents of cells migrate (or go) as input”?

The following graphics answers this question:

The value from each cell occurs exactly twice in the row below, once directly below, and once below and to the right.

Therefore, the sum of each row must be twice the sum of the row above. This representation also can be displayed in spreadsheet programs immediately. In Excel, the menu item Trace dependents will display the corresponding arrows.

Looking at this graphical representation we notice that it proves the following equation:

\[ \sum_{i=0}^{n} B(n, i)=2 \sum_{i=0}^{n-1} B(n-1, i) \]

Additionally taking into account

\[ \sum_{i=0}^{0} B(0, i)=1 \] (the sum of the top row is 1) we just proved

\[ \sum_{i=0}^{n} B(n, i)=2^{n} \]

The mathematically inclined reader by now has noticed that the table we are studying is Pascal‘s triangle, and therefore we proved an equation about the sum of binomial coefficients.

The standard proof (using the quotient of factorials representation of the binomial coefficients) of this equation is hard to understand for most nonmathematicians, and it also does not support insight into the underlying structure. Our “visual proof” helps to understand the underlying reason for the relation between row sums.

We also can use the table and arrow representation to show that the numbers in our table are the coefficients of the powers of \(x\) in the expanded version of the polynomial \((1+x)^{n}\). We simply write the coefficients of a polynomial in columns of a table.

As an example, the polynomial \(p(x)=2+3 x+x^{2}\) is represented by the following table:

Multiplying \(p(x)\) by \(x\) yields the polynomial \(x p(x)=x\left(2+3 x+x^{2}\right)=2 x+3 x^{2}+x^{3}\) with table representation

The coefficients of \(x . p(x)\) are the coefficients of \(p(x)\) shifted right.

Computing \((1+x) \cdot p(x)=(1+x) \cdot\left(2+3 x+x^{2}\right)=2+5 x+35+x^{3}\) is illustrated by the following table:

Omitting the intermediate row for \(x \cdot\left(2+3 x+x^{2}\right)\) we see that multiplying a polynomial with \((1+x)\) can be represented with the arrow diagrams in the following table:

Applying this to the powers of \((1+x)\) gives us the following arrow table:

In a very literal sense we can see that the coefficients of \((1+x)^{n}\) are given by the table we described at the beginning of this section, Pascal's triangle.

3. Spreadsheets and mathematical notation

In my classes, I regularly ask the students to compute the following numbers:

\(3.3-4.2=\)

\(5.5-6.4=\)

\(8.8-9.7=\)

Usually it takes quite a bit of time until the students recognize the pattern.

In most cases, students do not see the connection to the equation \((a+1) \cdot(a-1)=\mathrm{a}^{2}-\mathrm{b}^{\circ}\) 1 which is a special case of the equation \((a+b) \cdot(a-b)=a^{2}-b^{2}\).

Quite often, changing the representation helps:

\(3.3=4.2+1\)

\(5.5=6.4+1\)

\(8.8=9.7+1\)

With this representation, the following pattern can be noticed:

Using a graphical symbol as placeholder (instead of a variable letter) seems to allow the students to recognize the pattern more easily. The graphical symbol more clearly indicates the role of a placeholder and does not - as the variable letter does - trigger the association to algebraic transformations.

Starting with the concept of a placeholder, the path to spreadsheets is very short. A cell in a spreadsheet is a place holding a value. Spreadsheets use this concept in very pure form.

An additional important aspect of spreadsheets is that the role of a number or an expression in a mathematical structure is not represented by a name but by the position within the system. This has been illustrated very explicitly by examples with the binomial coefficients in the previous section.

Further examples

Paper format A4

How long and how wide is a sheet of paper in format A4?

It helps to know that the A format series is defined by a geometric property.

Folding a sheet parallel to the short edge in the middle of the long edge produces a smaller rectangle with the same proportion of the edge lengths.

Before and after folding the sheet looks like this:

To check that the “folded half” sheet has the same proportion for the edges as the unfolded sheet we put the smaller sheet on top of the larger sheet. The corner of smaller sheet then must lie on the diagonal of the larger sheet, like this:

Writing the length of the edges in a table we have:

The ratio of the edge lengths in both rectangles needs to be equal, therefore in the following table the expression in the rightmost columns should be equal.

Goal seek in Excel (or an equivalent procedure in most other spreadsheets) can solve this problem numerically very easily. One only has to declare the cell with the “short edge” as changeable and set the goal that the difference of the two values in the rightmost should be 0.

The value computed as solution is the length ratio of the edges. The length of the shorter edge is computed by multiplying the length of the longer edge by this value.

So multiplying the long edge length of the large sheet gives the short edge length of the large sheet, which is identical to the long edge length of the small sheet. Multiplying this number with the length ratio is the short edge length of the small length.

So multiplying the long edge length of the large sheet with the length ratio twice gives the short edge length of the small sheet. Since this length also is half of the length of the long edge of the large sheet, we see that the square of the length ratio is \(\tfrac{1}{2}\), and the length ratio is \(\sqrt{\tfrac{1}{2}}=\tfrac{\sqrt{2}}{2}\).

Using a spreadsheet program we can compute this value either by using the square root function or by using Goal seek to solve the equation numerically.

To find the lengths (and not only the length ratio) we need more information. The standard for the A series of paper formats states that the area of A0 is \(1 \mathrm{~m}^{2}\). So we can set up a spreadsheet formula for area \(=\) length. lenght. \(\sqrt{\tfrac{1}{2}}\) and use Goal seek to change the value of length until the area is 1. Similar considerations can be used to compute the Golden Ratio.

5. The Golden Ratio

We start with a rectangle and create a larger rectangle by adding a square at the longer edge of the rectangle.

We want the larger rectangle to have the same edge length ratio as the smaller one.

We can check this with putting the smaller rectangle on top of the larger triangle. The edge length ratio is the same if the corner of the smaller rectangle lies on the diagonal of the larger rectangle.

The table for the length ratios is

Using 1 as fixed short length and declaring long edge as the changeable value we can use Goal seek to find a value for the long edge which makes the difference between the values in the last column 0.

This solution is the numerical value for the Golden Ratio.

Geometric sequences and series

Geometric sequences can be implemented in spreadsheet very easily. Writing all the elements of a sequence in a column we see that any element (except the top one) is the previous element multiplied with a constant.

A formula referring to the value above and a constant is easily implemented by a spreadsheet.

The geometric series is the running sum of the geometric sequence and it also can be implemented with spreadsheets in a very natural way.

Dynamic systems

The balance of a savings account can be seen as a dynamic system.

The system variable balance changes over time according to fixed rules.

Setting up for each period a row in a spreadsheet table we can model the system by computing the new balance from the previous balance. So compound interest calculations are recursive in a very natural way and spreadsheets are especially helpful in illustrating this fact.

More examples can be found Neuwirth und Arganbright (2003).

Concluding remarks

Writing an article about spreadsheets - a dynamic medium - published as a paper - a static medium - is definitely difficult. It can be compared to writing about music with words only. A score (the representation of music in a static medium) is not the music. It takes practice to be able to translate written or printed scores into performances that one can listen to.

This comparison shows one of the fundamental problems of the didactics of mathematics. When we use computers as part of learning mathematics (not only as enhanced and enlarged pocket calculator, but as tool for dealing with structures) then the classical medium for communicating mathematics (printed or written materials) are not able to transport the process of mathematical activities to their full extent.

Historically, the strength of mathematics is representing dynamic processes (processes changing something in time) with static means.

Before computers, there were no possibilities for interactive dynamic representations. Software changed this.

There is another argument for using spreadsheets when teaching mathematics: spreadsheets are the most widely used tool for anything numerical in everyday life of most people, especially with regard to any economic activities, but also in science and research. Mathematics in school should connect here and demonstrate that these tools also can be used to do “real mathematics”.

When schools only use special mathematical software, then mathematics sends the message that what happens in mathematics has no connection at all with “real life” outside of mathematics.

This danger becomes very real when mathematics classes use only computer algebra systems (CAS). Only very few students will use this kind of tool after school. Spreadsheets can help the learner acquiring skills and competences, which they can use later in their life in many situations outside of “hardcore mathematics”.

REFERENCES

Neuwirth E., Arganbright D. (2003) Mathematical Modeling with Microsoft Excel Duxbury, Pacific Grove.

Borovcnik M., Neuwirth E. (2008) Rekursive Zugänge zu Wahrscheinlichkeitsproblemen und ihr Potential zur Modellbildung Schriftenreihe zur Didaktik der Mathematik der Österreichischen Mathematischen Gesellschaft (ÖMG), 41, S. 1 – 21.