We can work this sums out quite easily. First, we'll need some text headings.
- Click inside cell A13 and enter the text "Cost of Addiction"
- Merge the text into one cell, just like you did in the last part
- Format the text: bold, centre, a coloured font or background, if your prefer
- When you have finished, press the Return key on your keyboard
We'll now copy and paste some text.
- Click inside cell A4, then highlighted the cells down to A7 (the cells A4, A5, A6 and A7 should be highlighted)
- From the menu bar, click on Edit
- From the menu that drops down, click copy
- There should be some sort of animated line around your highlighted cells - the marching ants!
- When you see the marching ants, click inside cell A15 of your spreadsheet
- Press the Return key on your keyboard
- The text should have been copied to the new location, and your spreadsheet will look like the one below
We'll enter some more headings, so that we can work out how much this addiction is costing.
- Click inside cell B14 and enter Price
- Click inside cell C14 and enter Number
- Click inside cell D14 and enter Cost
The spreadsheet now looks like this:
Under "Price" is where we'll put the price of each chocolate bar. Under "Number" we'll put the number of each bar eaten in a week. Under "Cost" is where we'll find out how much is being spent each week on the individual chocolate bars. In other words, how much is being spent on Mars Bars, how much on Twix, how much on Bounty Bars, and how much on Others.
So enter some prices. Our researchers have been out and about eating chocolate bars. They found that prices for each chocolate bar are these:
| Mars Bars | .35 |
| Twix | .29 |
| Bounty | .32 |
| Others | .40 |
So enter those numbers in the prices column. Don't forget to put "dot price" and not just "price". So it would be .35 for mars bars and not just 35. When you have finished entering the prices, you can format them all as Currency. You learnt how do this in the first section. (Format > Cells, then click the Number tab strip form the dialogue box. Click Currency under Category, and format it to 2 decimal places.
Your spreadsheet from the previous part of the lesson should look like the one below.
For the Number column, you can Copy and Paste the figures from the Individual Totals column.
- So highlight the four totals in the J column
- Click on Edit from the menu bar
- Click on copy
- You get the marching ants again around your highlighted cells
- Now click inside cell C15 under the Numbers heading
- Hit the Return key on your keyboard to paste the numbers to the new location
- Something odd happens. Your spreadsheet will look like the one below:
So what went wrong? Why did we get all those #REF comments in the cells?
The problem is that there's a formula underneath the numbers we copied. And Excel tried to paste the formulas as well as the numbers. But the formulas are for the J column, and the cell references belong to that column. They don't belong in the C column. So Excel gives you the #REF error comments.
To solve the problem, do this:
- Click on Edit from the menu bar. From the drop down menu, click on "Undo Paste"
- Highlight the four cells in the J column again
- Click on Edit from the menu bar. From the drop down menu, click on "Copy"
- Click inside cell C15 and highlight the four cells down to C18
- Click with your right hand mouse button anywhere in the highlighted area. A menu pops up, as in the picture below:
- When you see the pop up menu, click on Paste Special with your left hand mouse button
- A dialogue box pops up - Paste Special
- Right at the top of the dialogue box there will be a black circle next to the word "All"
- Click the word "Values"
- Then click the OK button at the bottom
When you click the OK button, only the values from the J column cells will be pasted, and not the formulas underneath them.
So your spreadsheet should now look like this:
With only four values to copy and paste, it's probably easier just to type then in again! But if you have lots of values to copy and paste, then Paste Special comes in really handy.
No comments:
Post a Comment