Ranges and expressions 2
Spreadsheet programming
Last updated
Spreadsheet programming
Last updated
Next we create a new column and label it “time”, as a mnemonic. We enter the expression = A2 / Max( A:A) and expand the range to match the length of the “index” column. The expression computes the division between a cell’s value, on the left, by the maximum number found in the entire left column, hence A:A.
Doing so, we end up with a new range starting always from zero and ending always in one, independent of how many rows we expand columns A and B together! The operation of taking a range and mapping it onto [0.0, 1.0] or unit range is called normalization.
What we have accomplished is to take the unbound progression of natural numbers, i.e. they are infinite, and bound it into a finite real domain. Its label was named “time” because if we consider the values created as percentages, i.e. from 0% to 100% they are capturing the notion of progress towards completing a task in time.
Practice
Construct a range using the same process used for the “index” column but start from any integer value, say 8 to 16 or -6 to 21.
Create integer ranges that do not step forward one by one but perhaps at smaller or larger step-size, or even marching backwards.
Consider a range that does not start from zero, say from min_value to max_value. Create an expression that computes the equivalent normalized range for the “time” column.
Assuming an integer number range from min_value to max_value (say -10 to 10); given a value (say 0.25) from the equivalent normalized range, can you determine its index value?
Next enter the label “angle” in column C and expand the expression = B2 * 2 * PI( ) as seen below. This produces a transformation from the [0, 1] range to [0, 2π]. We thus converted an abstract bounded number sequence into a semantically angular range, with implied units of radians. Operations that involve only additions and multiplication are known as linear. Visually, you may plot the time/angle range-pair and verify that it will trace a line, in this particular case through the origin.
Conceptually, this transformation is also very important. The core idea here is that in computation, we always start with abstract number but progressively apply our own conventions to attach semantic meaning. We call this modelling. In this way, as long as we stay faithful to our conventions, we can represent such data such as text i.e. sequences of characters; geometric entities, such as points and vectors; colours for digitized pictures etc.
Next we will create two new columns to map the angular sequence into a cosine and a sine ranges. Notice that the trick we are playing here is writing one expression for the first cell of each column, relating horizontally with previously computed values, and expand the ranges. What we are not doing is filling the table cell by cell. Instead we let computation logic flow automatically.
Practice
Spreadsheets support a large number of mathematical functions. Construct some new columns combining and plotting some of those functions. You can use the “fx” button to explore the full list of available functions.
The intermediate columns produced can be collapsed into a single expression. Try to map directly between the “index” and the “cosine” by composing multiple expressions.
