Using simple Excel© formulas to perform calculations



Using simple Excel© formulas to perform calculations

Quick recap

In previous Modules you have learned how to:

• create and save a simple Excel© workbook

• edit a worksheet

• format a worksheet

In this Module you will:

• learn how to enter simple formulas so that Excel© will then perform calculations for you

• begin to see the real power of Excel© and how you can use it to make your work easier

Getting started

Please open the Microsoft Excel© Workbook entitled Brokebuster.xls.

This is the only Workbook you will need for this Module.

The Scenario

The information contained on Worksheet 1 of this Workbook represents the weekly takings of the branch of Brokebuster Video Rentals where you are an employee.

Usually, the tasks of summarising the week’s sales are carried out by another colleague using a calculator and a pencil. But this week, the Manager has specifically asked you to do it.

As the office PC includes Microsoft Office©, you realise that you can complete the required tasks quickly and impress your manager by utilising Microsoft Excel©.

[pic]

Figure 1

Task 1

As a guide, we have already entered two of the required weekly Takings totals for you, namely those for the Video/DVD Rentals and Soft Drinks rows.

1. Use your Mouse to click on cell I4. I4 is the cell that contains the total weekly Takings figure for Video/DVD Rentals.

2. Looking up at the toolbar, you will see the formula that Excel© is using to calculate and arrive at the total takings figure of £ 782. The feature you are looking at is called the ‘Formula Bar’ and it is prefixed with the symbols ’f x ‘.

3. You can see that this formula, =B4+C4+D4+E4+F4+G4+H4, specifically adds the figures contained in the seven cells present on that row that contain the daily figures for Video/ DVD Rentals.

4. The ‘=’ sign means that the result of adding these figures is then displayed in cell I4.

5. Your task now is to double-click on the empty cell I6 and, using the formula described above as a guide, enter a formula that will produce a total figure for the Video/ DVD Purchases row (i.e. row 6).

6. Remember that when you have completed your formula, you will need to use the Mouse to click elsewhere on the Worksheet in order that the result produced by your formula (rather than the formula itself) will be displayed in cell I6.

[pic]

Figure 2

Task 1: The Solution

The correct total figure for the ‘Video/ DVD Purchases’ row should be: £78.49.

The correct formula that produces this total is: =B6+C6+D6+E6+F6+G6+H6.

If you have any answer other than 78.49 appearing in cell I6 you have used the wrong cell references and you will need to troubleshoot your formula.

Try checking the following:

1. Click on cell I6 and, looking up at the toolbar you will see the formula that Excel© is using to calculate the total that currently appears in cell I6. See the spreadsheet in Figure 2, above

2. If any of the cell references in the formula do not relate to cells on row 6 then you need to correct them so that match the correct formula as given above

3. After correcting your formula, you will need to use the Mouse to click elsewhere on the Worksheet in order that the result produced by your formula (rather than the formula itself) will be displayed in cell I6

Task 2

While adding individual cell references to produce a result in a formula is intuitive and feels familiar it is, as you have seen, also rather laborious to type in! Also, the number of cell references that you need to include can result in unforeseen errors.

For our next formula we are going to adopt a more sophisticated approach.

This formula will be quicker to type and will demonstrate the power of Excel© far better than the simple arithmetical approach we have seen thus far.

Using the SUM function

As stated above, the second of the Totals that we have already calculated for you in the Worksheet is that for Soft Drinks. That Total is displayed in cell I8.

1. Use your Mouse to click on cell I8.

2. Looking up at the toolbar, you will see the formula that Excel© is using to calculate and arrive at this weekly takings figure of £ 164.

3. You can see that this formula, =SUM(B8:H8), specifically sums the figures contained in the range of cells starting at cell B8 and ending at cell H8. This range includes those cells on row 8 that lie in between cell B8 and cell H8.

4. The ‘=’ sign means that the result of the sum of these seven figures is then displayed in cell I8.

5. Your task now is to double-click on the empty cell I10 and, using the formula described above as a guide, enter a formula incorporating the SUM function that will produce a total figure for Ice Cream.

6. Remember once again that when you have completed typing your formula, you will need to use the Mouse to click elsewhere on the Worksheet in order that the result produced by your formula (rather than the formula itself) will be displayed in cell I10.

[pic]

Figure 3

Task 2: The Solution

The correct total figure for the ‘Ice Cream’ row should be: £ 84.

The correct formula that produces this total is: =SUM(B10:H10)

If you have any answer other than £ 84 appearing in cell I10 you have used the wrong cell references and you need to troubleshoot your formula so that it matches the above solution.

However, the simplicity of the SUM function means that there is much less likelihood of you having made an error in the first place!

Task 3

Well done! Your Worksheet is really starting to take shape now!

Only one of the weekly takings totals remains to be calculated. That is the total for Popcorn sales and we want the result to appear in cell reference I12.

Once again we will be using a formula to do the hard work for us but this time we will look at a variation of the SUM function which includes a feature you have not met before: AutoSum.

Applying the AutoSum feature

Just as the SUM function is more efficient than typing every single cell reference in a range into a formula, so using the AutoSum feature makes utilising the SUM function even quicker and more convenient.

1. Use your Mouse to click on cell I12.

2. Click on the AutoSum key on the Toolbar. This is the key marked with the ∑ symbol (this is the Greek letter Sigma which actually means ‘the sum of’)

3. You will see that a complete formula, namely =SUM(B12:H12) has now automatically appeared in cell I12 and in the formula section of the toolbar.

4. Note that the cells on row 12 – the cells that have been included in the range that the AutoSum function has used in our new formula – now have a ‘moving’ border around them. The motion of this border indicates that these cells are currently ‘active’.

5. In this instance we cannot simply click elsewhere on the Worksheet to see the result of our formula (as we have done previously). This is because the AutoSum, which is still active, will assume we have changed our mind and want to include the new cell we have just clicked on.

6. Before our formula is properly completed we have to indicate that our use of AutoSum in this formula is now finished. The simplest way to do this is simply to use the Mouse to click in the formula section on the toolbar. This click indicates that the formula is now concluded

7. All that is left now is to click anywhere else on the Worksheet and then, as previously, we will see the result produced by the formula in cell I12 - rather than simply the formula itself.

[pic]

Figure 4

Task 3: The Solution

The correct total figure for the ‘Popcorn’ row is: £ 45.

As you have just seen, the correct formula that produces this total is: =SUM(B12:H12) and you achieved this faster than either of the previous formulas by using the built-in AutoSum feature.

Well, you must be feeling pretty good! You have calculated the required figures in record time! Your Boss is sure to be pleased.

Unfortunately, your Boss sees your efficiency as an opportunity to give you more work!

He now also wants you to:

1. Produce a figure for the total Takings figure for the entire week

2. Brokebuster have promised to give one fifth of this week’s income from every one of their branches to the charity ‘Help for Heroes’. So, the total takings figure will need to be divided by five to arrive at this charitable donation figure.

3. And finally, you are required to calculate the net income for the branch for the week - taking into account the deducted charity donation you have just calculated!

Preparation required prior to Tasks 4, 5 and 6

Prior to coding the three formulas that will satisfy your manager’s new requirements you have a little editing to do.

In the cells immediately to the left of where the results of your three new formulas will appear, you need to enter appropriate text to explain what the figures represent.

As we saw in an earlier Module, ‘labels’ of this kind are essential. While working on a Workbook you understand what you’re doing and why, but if you return to a workbook after some time has elapsed (or if someone else is seeing the workbook for the first time) the figures present there may have little or no meaning.

So, do the following before proceeding onto Task 4.

1. Click on cell A15 and type ‘Total Income:’

2. Click on cell A16 and type ‘Donation to Charity:’

3. Click on cell A17 and type ‘Net Income:’

If you like, you can change the colour of the text of these three labels to match the colour of the ‘Takings’ column title which is in cell I3 – or you could even choose a different colour for each of the three labels. It’s up to you.

Figure 5

[pic]

Task 4

The first of your new tasks is to produce a formula that will calculate the total Takings figure for the entire week.

Think about what you are being asked to calculate here.

You need to calculate the total of the five figures that are present in column I. Those five figures represent the takings for the week for the five principal services and products that Brokebuster sells.

The knowledge you have gained regarding the SUM function is sufficient for you to now produce a formula that will answer this question.

You should be able to code your formula and then go straight to the solution (below) without following step by step instructions on how to do it.

Your total should appear in cell reference B15.

[pic]

Figure 6

Task 4: The Solution

The correct total figure for the ‘Total Income’ is: £ 1153.49.

The formula that gives us this figure is =SUM(I4:I12). This adds together all the figures that are present in column ‘I’. Or, to put it another way, it adds together the figures contained in those cells that are present in the range of cells starting at cell I4 and ending at cell I12.

Did you get it right? If so, congratulations!

If you have any doubts about this figure you can check the accuracy of it by using a calculator!

Task 5

The second of your new tasks is to produce a formula that will calculate the charitable donation for the charity ‘Help for Heroes’.

Again, think about what you are being asked to calculate here.

Bear in mind that you already have the figure for the total takings. That is the figure you have just coded a formula to calculate and it is now present in cell B15.

So, to calculate the charitable donation you require a new formula that takes the figure from B15 and simply divides it by 5. The resulting figure from your formula will be your answer. This result should appear one row down in cell reference B16.

Task 5: The Approach

Up to this point we have used addition to arrive at our desired totals.

• In cells I4 and I6 we specifically used the addition sign: ‘+’

• In cells I8, I10 and I12 we used the SUM function to add the figures for us

Now you need to take a previously calculated figure (that in B15) and divide it by 5.

In Excel© the mathematical sign that we use for division is the ‘/’, also known as a forward slash. The key for this is present on the right hand side of your keyboard (it usually shares the same button as the ‘?’ symbol).

1. Use your Mouse to double click on cell B16.

2. type the following formula in: =B15/5

3. Remember that as previously, when you have completed your formula you will need to use the Mouse to click elsewhere on the Worksheet in order that the result produced by your formula (rather than the formula itself) will be then displayed in cell B16.

[pic]

Figure 7

Task 5: Considering your Solution

The correct figure for the charitable donation is: £230.70. I’m sure ‘Help for Heroes’ will be pleased!

It is worth pausing for a minute to consider what your latest formula is actually doing.

Your formula, ‘=B15/5’, takes the cell reference of cell B15 and then, using the ‘/’ (forward slash) symbol, divides it by 5. Note that this last element is not a cell reference. It is the normal mathematical value 5 and we can use values such as this in formulas where it is appropriate – not everything in Excel© is a reference to the contents of a cell.

Task 6

Your third and final new task is to produce a formula that will calculate the net Takings figure after the donation to charity has been taken into account.

Again, think about what you are being asked to calculate here.

You need to subtract the charitable donation figure (which you have just calculated and is present in cell B16) from the total takings figure. The latter figure is one you calculated earlier for Task 4 and is present in cell B15.

Just as the symbol for division is ‘/’, so the symbol for subtraction is rather unsurprisingly ‘-‘. So, you can use this minus sign ‘-‘ in your final formula.

The knowledge you have gained in this tutorial is sufficient for you to now produce a formula that will satisfy the requirements of this task.

You should be able to code your function and then go straight to the solution (below) without following step by step instructions on how to do it.

Your total should appear in cell reference B17.

[pic]

Figure 8

Task 6: The Solution

This is the correct solution:-

• The correct figure for the net income is: £ 922.79.

• This figure should be present in cell reference B17.

• And we achieve this result by using the formula: =B15-B16.

Well done, your Manager should be very pleased!

Disclaimer: Pauly D’s Training Needs Incorporated is an entirely fictitious training organisation.

Any resemblance between PDTN and any real training company is entirely coincidental.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download