Spreadsheets vs Programming Languages

From CDS 130

Jump to: navigation, search

Spreadsheets vs Programming Languages


  1. Objectives
  2. Motivation
  3. Priming questions
  4. Notes
    1. Assignment
    2. Variables, Arrays, and Functions
    3. Functions
    4. Question
    5. Answer
    6. Advantage Example I
    7. Advantage Example II
    8. Advantage Example III
  5. Questions
    1. Addressing
    2. Alternative calculations
    3. Functions

1. Objectives

  • To introduce programming terminology with spreadsheets.
  • To introduce the concept of assignment.
  • To introduce the concept of a variable.
  • To introduce the concept of an array.
  • To introduce the concept of a function.

2. Motivation

  • Solving science problems often involves the use of a programing language.
  • Many basic science problems can be solved using a spreadsheet program, but programming languages are generally used for advanced problems.

3. Priming questions

  • What is the most complex thing that you have ever done with a spreadsheet?
  • Suppose that you were given the task of doing 100 two-bit addition problems using the switches and light bulb configuration discussed previously (instead of a calculator). How long would it take you to enter the results (by flipping switches) and write down the results?
  • What programming language have you used? What did the programs that you wrote do?

4. Notes

4.1. Assignment

A computer program is a set of instructions that the programming language interpreter can convert into computer instructions.

There are many programs that will allow you to say

A = 1
B = 2
C = A+B

The above program says "Assign the value of 1 to a variable named A. Assign the value of 2 to a variable named B. Assign the value of the sum of the value of A and B to a variable named C.

In a spreadsheet it could be Image:aplusb.png

4.2. Variables, Arrays, and Functions

Programming languages also have variables, arrays, and functions. At this point, you do not need to know their exact definition; instead you should know how these terms apply to a spreadsheet.

  • Each of the cells represents a variable. The contents of the cell (usually a number) can vary.
  • The collection of values from A1 to A7 is an array.
  • The math function SUM takes an input of the array values A1, A2, ..., A7 and returns the sum.


4.3. Functions

  • A function takes inputs and returns one or more outputs.
  • Think of a function like a automobile factory. It takes inputs of metal, plastic, glass, etc. and then outputs a car.
  • In the example above, the sum function took a list of numbers and output ("returned") the sum of the numbers.
  • There are many types of functions. For example, there are functions that will take a list of numbers and return their product.
  • In computing functions take inputs of variables, arrays, and functions, and return variables, arrays, and functions.

4.4. Question

  • How would you compute the sum of 1 through 100000 using a spreadsheet?
  • Suppose that wanted to create a list of random numbers and add all values in the list greater than 2. How would you do this with a spreadsheet?

4.5. Answer

You could do these two problems using a spreadsheet. However, it would not be easy and you would probably end up using a macro. A macro allows you two use a programming language (such as Visual Basic) inside of a spreadsheet.

So why not always use a spreadsheet?

  • Spreadsheets were designed to solve Accounting problems.
  • Spreadsheets can be used to solve Science problems, but programming languages have advantages.

4.6. Advantage Example I

  • One of the advantages of using a scientific programming language is that common operations can be done with simple syntax.
  • Consider the problem of adding the numbers from 1 to 7.

In MATLAB we could do this creating an array variable by entering

A(1) = 1
A(2) = 2
A(3) = 3
A(4) = 4
A(5) = 5
A(6) = 6
A(7) = 7
sum( A(1:7) )

or, using short-hand notation

A = [1,2,3,4,5,6,7]

or, shorter-hand

A = [1:7]

or, shorter-shorter-hand


4.7. Advantage Example II

  • Another advantage of scientific programming languages is that short-cuts are often implemented behind-the-scenes to do the computation more quickly.
  • As an example, there are several ways of adding integers from 1 to 7.

You could do six additions

s = 1;
s = s+2;
s = s+3;
s = s+4;
s = s+5;
s = s+6;
s = s+7;

or three multiplication calculations [1] (where N=7)

s = 0.5*N*(N+1);

Suppose that N=1000. If you used the first method, you would need to do 999 additions. If you used the second method, you would need to do three multiplications. Some scientific programming languages will recognize when short-cuts such as these can be taken.

4.8. Advantage Example III

  • Scientific programming languages usually have a wide selection of domain-specific functions.
  • If you need to do a certain calculation (such as summing numbers), and a function exists already, you can use that function instead of writing our own code to do the calculation. (For example, if the SUM function did not exist in Excel, you would need to type =A1+A2+A3+A4+A5+A6+A7 instead of SUM(A1:A7).
  • MATLAB has many functions for doing engineering calculations.
  • The R language has many functions for doing non-standard statistical calculations.

5. Questions

5.1. Addressing

In a spreadsheet, the value stored in the first column and second row is accessed by typing =A2 in another cell. In MATLAB/Octave, the second value in an array is accessed by typing A(2) on the command line.

Based on this analogy, explain why in MATLAB/Octave the following commands don't make sense:

  • A(0) = 10
  • A(1.25) = 10

5.2. Alternative calculations

Give an example of a calculation that can be done in more than one way using a spreadsheet. Which method do you think is better or preferred and why?

5.3. Functions

Given an example of a function other than sum that is available in a typical spreadsheet program.

Personal tools