Refresh your Excel skills from handouts 1 and 2 by completing the following task:
You have been asked to edit a spread sheet to calculate the cost of individual artificial flowers.
You have been given the file with the information.
Enter a formula to calculate the Individual Flower Price.
Individual Flower Price is Price divided by Flowers Per Stem.
Your spread sheet should now look like this:
The “IF” Function
The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE.
In the example below, we are testing whether cell A1 is larger than 10 (A1>10). The cell is 12 and therefore the computer returns the value “Correct”.
Using the ‘Flowers’ spreadsheet, you are now given the following task:
Only flowers with individual flower price of less than 25p will be bought in the future.
Enter a function to show which flowers should be re-ordered.
For individual flower price of less than 25p show the word “Re-order” and show “Discontinue” for all others.
Firstly you need to decide where these words should be displayed. It makes sense for this function to be in the next column (column F) and you therefore have to give it a title. As it is about ordering new flowers the title could be for example “New Order”.
You now need to position yourself in the cell where the first IF Function will go into. Here it is cell F2. You can find the IF function under the tab Formulas. It is in two places: either under the purple book called Logical, or under Insert Formulas. Click on either one of them and select IF.
After you have clicked on the IF, the following table will appear. It contains three rows.
In the first row (called Logical test)
you need to put which cell has to
be how much. In our example:
Individual Flower Price has to be
less than 25p.
You therefore need to put:
E2<0.25 in the first row of the table.
In the second row you need to put the