.. Copyright (C) Google, Runestone Interactive LLC
This work is licensed under the Creative Commons Attribution-ShareAlike 4.0
International License. To view a copy of this license, visit
http://creativecommons.org/licenses/by-sa/4.0/.
What Is A Formula?
==================
Formulas are a way of manipulating data in Sheets. A formula processes data in
the same way a calculator does, but in a more organized and useful way that
allows you to combine calculations.
All formulas start with the ``=`` character. This tells Sheets that this cell is
a formula, rather than raw data. What makes formulas so useful is that the input
to a formula can be new data (e.g. ``=1+1``), or existing data from cells (e.g.
``=A1+A2``), or a combination (e.g. ``=A1+1``). Below are some illustrative
examples of how formulas work.
.. spreadsheet:: formulas
:colwidths: 300,300
:coltitles: 'formula','result'
"=1+1",2
"=2^2",4
"=(1+1=3)",FALSE
"=CONCATENATE(""abc"", ""xyz"")",abcxyz
"=B2+B3",6
"=2^B3",16
"=-B6/10",-0.6
Note that if Sheets cannot parse or calculate your formula, you will see an
error. See :ref:`this section` for more on errors.
.. fillintheblank:: exponent_formula
Write a formula to calculate :math:`2^{10}`. |blank|
- :=2\^10: Correct
:2\^10: Incorrect: Don't forget the ``=`` symbol.
:x: Incorrect
.. fillintheblank:: multiplication_formula
Use Sheets to calculate :math:`-1.23 * 9.87` to 2 decimal places. |blank|
- :-12.14: Correct
:x: Incorrect
Example: Chocolate Cake
-----------------------
Suppose you are going to a party, and you decide to bake a cake. Your favorite
is chocolate cake, so you decide to make `this “quick chocolate cake”`_. You
note that the recipe serves 12, so you ask the party hosts how many guests are
attending. They say they will need 18 servings.
Since you are now more familiar with Sheets, instead of manually calculating,
for each ingredient, how to alter this recipe for 18 people, you use a formula.
Sheets allows you to perform the same calculation for all ingredients, with one
formula.
First, you enter the recipe for 12 servings into a table. (Note that for the
ingredients with fractional amounts like 2/3 cup vegetable oil, you need to use
a formula, namely ``=2/3``).
.. spreadsheet:: chocolate_cake
:colwidths: 300,300
:coltitles: 'ingredient','amount for 12 servings'
flour (cup),3
sugar (cup),2
cold water (cup),2
vegetable oil (cup),0.67
cocoa powder (cup),0.5
baking soda (tsp),2
white vinegar (tsp),2
salt (tsp),1
vanilla (tsp),1
Now, you know that for 18 people, you will need to scale up everything to 18.
You can do this by multiplying each ingredient by the scale factor of 18/12.
(You can think of this as dividing by 12 to get the amounts for 1 serving, then
multiplying by 18 to get the amounts for 18 servings.)
You can use column C to write a formula to get the amounts for 18 servings.
First, you need to write the formula for row 2, namely flour. The amount for 12
servings is in cell B2. You want to upsize this ingredient by a factor of 18/12,
so the formula is as follows.
.. code-block:: none
=B2*18/12
.. image:: figures/chocolate_cake_flour_for_18_servings.png
:align: center
So for 18 servings, you need 4.5 cups of flour. Now, it’s as simple as
copy-pasting this same formula to the other rows. You can do this using in-built
copy-paste functionality, or you can drag down from the corner of the cell.
.. fillintheblank:: chocolate_cake_cocoa_powder_for_18_servings
How many cups of cocoa powder do you need for 18 servings? |blank|
- :0.75: Correct
:x: Incorrect
The best thing about Sheets is that you could recreate this recipe no matter how
the numbers change. For example, suppose the hosts ask you to bring enough for
23.771 servings. (There are 24 people, but one person is on a diet, and has
asked for only 0.771 servings.) You can actually encode the number of servings
into the formula, so that the shopping list automatically updates whenever you
update the number of servings.
Use column D to show the amount required for the custom number of servings, and
use cell E2 to store the desired number of servings, as below.
.. image:: figures/chocolate_cake_custom_servings.png
:align: center
Now, you can write a formula in column D, similar to the formulas in column C,
except instead of **hardcoding** (entering a number manually) the number of
servings, use cell E2 to indicate the number of servings.
.. image:: figures/chocolate_cake_flour_for_custom_servings.png
:align: center
.. shortanswer:: dragging_formula_down
What do you think will happen if you drag this formula to the rows below?
As was done in column C to calculate the recipe amounts for 18 servings, try
dragging the formula in column D down to the rows below. You should observe that
in this case, copy-pasting the formula did not behave as you might have wanted
it to.
.. shortanswer:: why_dragging_formula_down_did_not_work
Why do you think copy-pasting this formula produced all zeros?
The formula in cell D2 references cells B2 and E2. When you copy-paste this
formula to D3, Sheets assumes you want the formula to update to reference cells
B3 and E3.
.. image:: figures/chocolate_cake_relative_referencing_not_working.png
:align: center
This is called **relative referencing**, because cell referencing in the formula
is relative to where the formula is used. (So when it is used in D3, it
references B3 and E3.) If you want a cell reference to *not* update when the
formula is copy-pasted, you need to use **absolute referencing**.
Sheets assumes references are relative. To tell Sheets to reference a cell
absolutely, you need to use the ``$`` symbol.
Consider the cell A1 used in a formula:
- If you use A1 in a formula, this is an implicit relative reference. Any cell
you copy the formula in will reference a different cell.
- If you use $A$1 in a formula, this is an absolute reference. Any cell you
copy the formula in will still reference $A$1.
You might be wondering what happens if you only include one of the ``$`` symbols
when referencing A1. This is called semi-absolute referencing, and can be used
to keep the row or column as an absolute reference while using the other as a
relative reference. `You can learn more about cell referencing here.`_
As an example, suppose you have the following sheet.
.. spreadsheet:: absolute_vs_relative_referencing_sheet
:colwidths: 200
1
2
3
4
5
6
7
8
9
10
.. TODO(https://github.com/RunestoneInteractive/RunestoneComponents/issues/901):
Change question to allow students to do this within the webpage.
.. shortanswer:: absolute_vs_relative_referencing
Copy this data to a spreadsheet, and in cell B1, enter the formula
``=A1*$A$2``. When you copy-paste this formula down, what numbers do you
expect to see?
In this chocolate cake example, you want column B to be referenced relatively
(so when you copy-paste to rows below, it updates itself), but cell E2 to be
referenced absolutely. So, the formula for cell D3 should be as below.
.. code-block:: none
=B2*$E$2/12
Copy-pasting this formula down for the other ingredients now works as intended.
.. image:: figures/chocolate_cake_amounts_for_custom_servings.png
:align: center
Now, if you update the number of servings you need in cell E2, the amounts for
the shopping list magically update themselves.
.. fillintheblank:: eggs_for_chocolate_cake
If 9.87 servings are required, you will need |blank| teaspoons of vanilla.
Use 2 decimal places in your answer.
- :0.82: Correct
:9.87: Incorrect: The original recipe made 12 servings, not 1.
:x: Incorrect
Doing all of these steps manually, for each ingredient, would be challenging and
may result in miscalculations. Doing it in Sheets might take some getting used
to, but once you have the hang of using formulas, it makes calculations like
this extremely simple!
Example: The Fibonacci Sequence
-------------------------------
While it is sometimes useful to use formulas as a type of calculator, formulas
are most powerful when extending the same operation to multiple rows or columns.
To illustrate this, consider `the Fibonacci Sequence`_.
**The Fibonacci Sequence is a sequence of positive numbers where each number is
the sum of the previous two numbers in the sequence.** The sequence starts with
the numbers 0, 1, 1, 2, and goes on infinitely. The sequence looks like:
.. code-block:: none
0, 1, 1, 2, 3, 5, 8, 13, 21, ...
.. fillintheblank:: fibonacci_next_numbers
The next two numbers in the sequence are |blank| and |blank|.
- :34: Correct
:x: Incorrect
- :55: Correct
:x: Incorrect
The Fibonacci sequence has a very special and fundamental relationship with
nature and art. It can predict, for example, the `number of rabbits in a
population`_. Fibonacci numbers also appear in `flower and leaf growth`_. The
real-life examples of the Fibonacci sequence don’t stop there. As the numbers in
the Fibonacci sequence get larger and larger, the ratio between consecutive
numbers in the sequence gets closer and closer to the `golden ratio`_. This
number is prevalent in pure mathematics, physics, nature, sculpture,
architecture, and art (for example in the `Mona Lisa`_). `You can read more
about the golden ratio’s appearance in classical and modern art here.`_
Now, suppose you wanted to generate the 50th number of the Fibonacci sequence.
You could do this by hand with lots of mental arithmetic, but this would take a
long time and could lead to errors. You could use a calculator, but that would
still require you to type about 50 individual sums, which would take a long
time. Let’s instead look at how using Sheets can help you save time and prevent
mistakes.
In Sheets, the Fibonacci sequence can be generated with one simple formula that
you can then extend to get as many numbers in the sequence as you need.
1. Start by manually entering the initial numbers of the sequence: 0, 1 in
cells A1 and A2.
2. The third number in the sequence is the sum of the previous two numbers, so
enter the formula ``=A1+A2`` in cell A3.
3. This formula in Sheets is ``=A1+A2``. Since each cell is referenced
relatively, it actually means “add the two numbers above this cell”.
Therefore, if you copy-paste this formula to the next cell A4, Sheets
interprets this as the formula ``=A2+A3``.
4. This can be extended to any number of cells. Drag the formula (or copy-paste
it) to all cells in A up to A50, to show the 50th Fibonacci number.
So the 50th number in the Fibonacci sequence is 7,778,742,049! In this example,
each cell adds the two previous cells, which in turn is used to calculate the
next cell. This is a type of **recursive formula**, where the same formula is
being used over and over, but with different inputs.
.. TODO(raskutti): Embed screencast of entire process above.
Calculating this by hand or using a calculator would have taken way too much
time and brainpower. Sheets lets you spend your time and brainpower on more
important things!
.. fillintheblank:: fibonacci_75th_number
What is the first digit of the 100th Fibonacci number? |blank|
- :2: Correct
:3: Incorrect: Remember 0 is the 1st Fibonacci number.
:x: Incorrect
.. _what_is_a_formula_painters:
Example: Painters
-----------------
To further illustrate how formulas can automate data processing, consider the
painters dataset again. Don’t worry if this example seems a bit daunting at
first; it is designed to be challenging.
Suppose you’re interested in studying only the most prolific French painters,
specifically French painters who produced over 200 paintings in their lifetimes.
(Note that there are a few painters with multiple nationalities listed. For the
purposes of this example, consider “French” to mean only French listed as a
nationality.)
First, let’s create a column that shows whether the painter was “prolific” by
your definition. Each new formula should be in a new column. You can use column
I to write this formula. Remember that you only need to write a formula for one
row and it can be copy-pasted to the other rows, as was done in the Fibonacci
example.
Start with the first observation row, row 2. (Row 1 contains the column
headings.) The painter is Amedeo Modigliani. Column H contains the number of
paintings. So, to see if Amedeo Modigliani painted over 200 paintings, column I
should contain the following formula.
.. code-block:: none
=H2>200
This formula evaluates to ``TRUE`` only if the value in H2 is greater than 200
and ``FALSE`` if it’s not. You can then drag down or copy-paste that formula to
the remaining rows.
Now, you can use column J to write a formula to show whether or not each painter
was French. Column E contains the nationality, so to see if Amedeo Modigliani is
French, column J should contain the following formula.
.. code-block:: none
=E2=“French”
This formula might be a bit confusing due to the use of two ``=`` characters.
The first ``=`` is telling Sheets that this cell contains a formula. The second
``=`` is a comparison (just like ``<`` or ``>``), comparing cell E2 to the word
“French”.
Once you have your answer for Modigliani, you can copy-paste this formula for
the other painters as well.
Going back to our original question (which French painters painted over 200
paintings in their lifetimes?), we must have a final answer to satisfy both
conditions:
- That the painter painted 200 paintings
- That the painter was French
This means both column I and column J must be ``TRUE``. Rather than manually
checking each answer, which can lead to mistakes, especially when you are
working with hundreds of rows of data, you can use the ``AND`` function.
.. admonition:: The ``AND`` Function
The ``AND`` function returns ``TRUE`` if and only if each statement separated
by a comma within its brackets are ``TRUE``. The syntax looks as below.
.. code-block:: none
=AND(statement1, statement2, ...)
In this case, ``AND`` is used to check that both I2 and J2 are ``TRUE``,
so the formula looks as below.
.. code-block:: none
=AND(I2=TRUE, J2=TRUE)
As usual, once this formula has been written for Amedeo Modigliani, it can be
dragged or copy-pasted for the other 49 painters. This gives a ``TRUE`` or
``FALSE`` in column K for all painters as to whether the listed painter was
classified as a prolific French painter.
You might, however, find that column K is still a little hard to digest, and
doesn’t present you with a succinct list of the prolific French painters in this
dataset. Adding another formula in column L can help with that! In this column,
you can write a formula that prints the artist’s name if they are prolific and
French, and prints nothing if not. This can be done using the ``IF`` function.
.. admonition:: The ``IF`` Function
The ``IF`` function is used to show different results based on a condition.
The syntax looks as below.
.. code-block:: none
=IF(condition that is true or false,
what to print if statement is TRUE,
what to print if statement is FALSE)
Since column K contains a condition that tells you whether the artist is
prolific and French, all you need to do is print the artist’s name (which
appears in column B) if ``TRUE``, and an empty cell if ``FALSE``. (The empty
cell actually contains the blank word “”.) So the formula in column L for the
first painter should look as follows.
.. code-block:: none
=IF(K2, B2, “”)
You can then drag down this formula to show the name of all French painters who
painted over 200 paintings.
.. mchoice:: french_painters
Who are the French painters who painted over 200 paintings?
- Titian
- Incorrect
- Pierre-Auguste Renoir
+ Correct
- Paul Gauguin
+ Correct
- Diego Rivera
- Incorrect
- Claude Monet
- Incorrect
- Edgar Degas
+ Correct
Of course, instead of using four formulas to find these painters, you could do
this all in one formula, by wrapping each step into the next. However, when
first starting, it helps to isolate each step into its own formula.
.. TODO(raskutti): Embed screencast of the entire exercise above, then using
only one formula.
.. shortanswer:: italian_painters
Find all (exclusively) Italian painters who painted fewer than 400 paintings.
.. _this “quick chocolate cake”: https://www.bhg.com/recipe/chocolate-cakes/quick-chocolate-cake/
.. _You can learn more about cell referencing here.: https://edu.gcfglobal.org/en/googlespreadsheets/types-of-cell-references/1/
.. _the Fibonacci Sequence: https://en.wikipedia.org/wiki/Fibonacci_number
.. _number of rabbits in a population: https://science.howstuffworks.com/math-concepts/fibonacci-nature.htm
.. _flower and leaf growth: https://www.mathsisfun.com/numbers/nature-golden-ratio-fibonacci.html
.. _golden ratio: https://en.wikipedia.org/wiki/Golden_ratio#Relationship_to_Fibonacci_sequence
.. _Mona Lisa: https://thefibonaccisequence.weebly.com/mona-lisa.html
.. _You can read more about the golden ratio’s appearance in classical and modern art here.: https://news.artnet.com/art-world/golden-ratio-in-art-328435