# Formula for Calculating Percentages - Excel

Document Sample

```					Contents

Using Help

Basic Formatting

Formulas

Using Formula

BODMAS

Using Functions and Referencing

Removing Errors

Charts

Calculating Percentages

Consolidation Exercise

Consolidation Exercise Solution

Excel Examples
Using Help

and range references copy the paragraph A reference identifies....... workbooks
are called links. And paste into the cell A1

2. Format cell A1 to turn on cell wrapping.
Pass mark required %             80

Test Taken           Mark%       Date
Module 1 Basic Concepts of IT     84
Module 2 Use PC and Manage Files 76
Module 2 Use PC and Manage Files 97
Module 3 Word Processing          78
Module 3 Word Processing          95
Module 5 Database                 86
Module 5 Database                100
Module 6 Presentations            97
95
Module 7 Information and Communication
Unit E Using IT                   72
Unit E Using IT                   75

Tests Passed
Tests Failed
Lowest Mark
Highest Mark
Average Mark
Total Tests Taken
Basic Formatting
1. Type the text Pass/Fail in cell C6 and apply Bold formatting

2. Use Format Painter to copy the formatting from cell A4 to cells A23:A28

3. Format cells B7 to B18 to be aligned center

4. Add the text ECDL Test Results in cell A1 format to Arial, font size 16, bold and Underlined

5. Re-size column A so all text is displayed

6. Format cells A7:D18 to display Outline and Inside borders

7. Left align the text in cells A6:D6

8. Format the text alignment of cells A6:D6 to 45°

9. Enter the date 6/8/05 in cell D7 and replicate to cells D8:D18

10. Format cells D7:D18 to display a Custom date in the format of dd-mmm-yy and remove the error
if necessary
Formulas
A calculation in Excel is called a

All formulas begin with an equals = sign, followe
calculation is made up of cell references separa
(+ add, - subtract, * multiply, / divide), e.g. =A1+
Formulas are used to calculate answers from nu
a worksheet. Changes made within referenced
to be recalculated.

1. Create the following worksheet in the columns

2. Select cell B6 and enter the formula
3. Add formulas to columns C6, D6 and E6 usin

iThe most common mathematical operation is
been simplified by the use of a Function called A
Toolbar. AutoSum adds the contents of cells aut

4. Delete the formula in cell B6.
5. Select cell B6, click on the AutoSum button,
Press <Enter> to complete the entry. The answe
A calculation in Excel is called a Formula.

All formulas begin with an equals = sign, followed by the calculation. The
calculation is made up of cell references separated by a mathematical symbol
subtract, * multiply, / divide), e.g. =A1+A2
Formulas are used to calculate answers from numbers that are entered into to
a worksheet. Changes made within referenced cells will cause the formulas
to be recalculated.

. Create the following worksheet in the columns and rows indicated.

. Select cell B6 and enter the formula =B4+B5, press enter.
. Add formulas to columns C6, D6 and E6 using the appropriate symbols.

The most common mathematical operation is addition. This calculation has
been simplified by the use of a Function called AutoSum,   on the Standard
Toolbar. AutoSum adds the contents of cells automatically.

. Delete the formula in cell B6.
. Select cell B6, click on the AutoSum button,   on the Standard Toolbar.
to complete the entry. The answer should be 9.
Company        Jan       Feb      Mar      Total
Items sold        6000     6400     7200           Using Formulas
Cost of Item         6        6        6
Income                                             1. Add a formula to cell
Workers            10        10       10           January
Weekly Pay        320       320      320
Wages                                              2. Replicate the formula in cell
Materials       24000     25600    28800
Spending
Profit                                             4. Replicate the formula in cell

5. Use the Sum Function in cell
Jan

6. Replicate the formula in cell

7. Add a formula to cell
and replicate the formula to cells

8. Insert Totals in cells
Using Formulas

1. Add a formula to cell B4 to calculate the income for sales in

2. Replicate the formula in cell B4 to the cells C4:D4

3. Add a formula to cell B7 to calculate the wages for Jan

4. Replicate the formula in cell B7 to the cells C7:D7

5. Use the Sum Function in cell B10 to calculate total spending for

6. Replicate the formula in cell B10 to the cells C10:D10

7. Add a formula to cell B11 to calculate the profit made for Jan
and replicate the formula to cells C11:D11

8. Insert Totals in cells E2, E4, and E7:E11
Order of Precedence – BODMAS

Excel uses an order of precedence when evaluating ca
summarised by the acronym
Subtract.

Use of parentheses (brackets)

To change the order of evaluation, enclose in brackets
first. For example, the following formula produces 11 b
before addition. The formula multiplies 2 by 3 and then
the following formula: =5+2*3

If you use parentheses (brackets) to change the order
together and then multiplies the result by 3 to produce
formula: =(5+2)*3

1. Insert the following data into this worksheet.

2. The reason for using column D for the numbers is th
spilled over into column C. Widen column B to hold all

3. Delete column C.

4. Calculate the Profit in C6
solution in cell D32

6. Format cell C6 to display currency with two decimal
BODMAS

el uses an order of precedence when evaluating calculations, which can be
marised by the acronym BODMAS – Brackets, Of, Divide, Multiply, Add,

of parentheses (brackets)

hange the order of evaluation, enclose in brackets the part of the formula to be calculated
For example, the following formula produces 11 because Excel calculates multiplication
re addition. The formula multiplies 2 by 3 and then adds 5 to the result. Try it by entering
=5+2*3

u use parentheses (brackets) to change the order of the calculation, Excel adds 5 and 2
ther and then multiplies the result by 3 to produce 21. Try it by entering the following

sert the following data into this worksheet.

he reason for using column D for the numbers is that the text is too long for column B. It
ed over into column C. Widen column B to hold all the text.

alculate the Profit in C6 (remember brackets). If you have problems solving this view the

ormat cell C6 to display currency with two decimal places.
ECDL Test Results                                          Using Functions and referencing
1. Use an IF function in cell
Pass mark required %                         80            required(B4) is greater than or equal to 80% if not d

Test Taken               Mark% Pass/Fail   2. Replicate the formula in cell
Module 1 Basic Concepts of IT             84
Module 2 Use PC and Manage Files          76               3. Change the formula in cell
Module 2 Use PC and Manage Files          97               Reference and replicate the formula to cells
Module 3 Word Processing                  78
4. Change the pass mark in cell
Module 3 Word Processing                  95
Module 4 Spreadsheets                     93               5. Use a CountIF function in cell
Module 5 Database                         86
Module 5 Database                         100              6. Use a CountIF function in cell
Module 6 Presentations                    97
Module 7 Information and Communication    95               7. Use a Min function in cell
Unit E Using IT                           72
Unit E Using IT                           75               8. Use a Max function in cell
and format the cell to display numbers and no decim

9. Use an Average function in cell
achieved
Tests Passed
10. Use a Count function in cell
Tests Failed
Lowest Mark
Highest Mark
Average Mark
Total Tests Taken

View IF Demo
Using Functions and referencing
function in cell C7 that will display Pass if Pass mark
is greater than or equal to 80% if not display Fail

the formula in cell C7 to cell range C8 to C18

3. Change the formula in cell C7 so the cell reference B4 is an Absolute
Reference and replicate the formula to cells C8:C18

4. Change the pass mark in cell B4 to 75 (Take note of the changes!)

function in cell C23 that will display a total of tests passed

function in cell C24 that will display a total of tests Failed

function in cell C25 that will display the lowest mark achieved

function in cell C26 that will display the highest mark achieved
and format the cell to display numbers and no decimal places

function in cell C27 that will display the average mark

function in cell C28 that will display the total tests taken
Pass mark required %                     #
Removing Errors
Test Taken                 P
Mark%ass/Fail      1. Remove the errors in this worksheet.
Module 1 Basic Concepts of IT            # Pass
Module 2 Use PC and Manage Files         # Pass
Module 2 Use PC and Manage Files         # Fail
Module 3 Word Processing                 # Fail
Module 3 Word Processing                 # Pass
Module 5 Database                        # Pass
Module 5 Database                        # Pass
Module 6 Presentations                   # Pass
Module 7 Information and Communication   # Pass
Unit E Using IT                          # Fail
Unit E Using IT                          # Fail

Tests Passed                                          7
Tests Failed                                          5
Lowest Mark                                         72
Highest Mark                                      100
Average Mark                                  #DIV/0!
Total Tests Taken                            #VALUE!
Removing Errors
Remove the errors in this worksheet.
Creating Charts
Leeds   Paris
Jan   60      68      Male     230   1. Display the
Feb   68      74      Female   465
Mar   72      77                     2. Create a
Apr   78      82
May   64      78                     3. Create a
Jun   84      84                     display data labels as
Jul   88      90
4. Change the line chart on this works
Aug   89      89
Sep   73      73
5. Give the column chart the title
Oct   70      78
Nov   67      78                     5. Change the
Dec   58      65
6. Change the column chart to a

7. Create a column chart within this wo
in the worksheet
Creating Charts

1. Display the Chart toolbar.

2. Create a Line chart within this worksheet using the the data range A3:C15

3. Create a Pie chart within this worksheet using the the data range E4:F5 to
display data labels as Percentages.

4. Change the line chart on this worksheet to the chart type column.

5. Give the column chart the title Test Results

5. Change the Column colours to any colour other than black or white.

6. Change the column chart to a Bar chart.

7. Create a column chart within this worksheet using the the data range A7:B18
in the worksheet Basic Formating.
Consolidation Exercise
Hourly Rate               Hours Worked Gross Pay
1. Insert the following data into the current workshee

Tax Rate

Gross Pay
Tax
Net Pay

2. In cell B4 insert the text Week No 1 and replicate

3. Calculate the gross pay in cell
the formula to cells D5:D7.

4. Use a formula in cell C11 to calculate total

5. Insert a formula in cell C12
reference to the Tax Rate.

6. Create a formula in cell C13 to calculate total net p

7. Format cell range C11:C13

8. Resize column B so all text is visible within the ce

9. Insert the text Average Weekly Earnings
C14 to calculate the average weekly earnings.

10. Change the page set-up to print to 1 page and la

11. Change to print preview and swap to page break

12. Freeze all rows above row 4.
olidation Exercise
rt the following data into the current worksheet.

insert the text Week No 1 and replicate to cells B5:B7.

ulate the gross pay in cell D4 making an absolute reference to cell A4 and replicate
.

C11 to calculate total Gross pay.

C12 to calculate tax paid on gross pay using an absolute

ate a formula in cell C13 to calculate total net pay.

C11:C13 to display currency £ Sterling.

so all text is visible within the cell.

Average Weekly Earnings in cell B14 and then add a function in cell
calculate the average weekly earnings.

up to print to 1 page and landscape.

ange to print preview and swap to page break view.

eze all rows above row 4.
Hourly Rate
5             Week No 1
Week No 2
Week No 3
Week No 4

Tax Rate
10
Gross Pay
Tax
Net Pay
Average Weekly Earnings
Hours Worked       Gross Pay
32                 =\$A\$4*C4
41                 =\$A\$4*C5
37.5               =\$A\$4*C6
50                 =\$A\$4*C7

=SUM(D4:D7)
=(C11*\$A\$10)/100
=C11-C12
=AVERAGE(D4:D7)
Module                              Tests Taken Percent of Total
Basic Concepts                                 6
Use the Computer and Manage Files              3                   1. Use a sum function in cell
Word Processing                                5
Spreadsheets                                   9                   2. Insert a formula for cell
Database                                      12                   percentage of total tests taken
Presentations                                  5
3. Format cell
Information and Communications                 8
Using IT                                       3
4 Replicate the formula in cell
Total Tests
1. Use a sum function in cell B10 to calculate total tests taken.

2. Insert a formula for cell C2 to calculate tests taken in B2 as a
percentage of total tests taken B10.

3. Format cell C2 to percentage format and no decimal places.

4 Replicate the formula in cell C2 to cells C3:C9.
Excel Examples

Examples of commonly used functions
IF
Average
Max - Min
Count
CountIF
Calculating Percentages

Examples of commonly used functions
When you create a formula that contains a function, the Insert Function       dialog box found on the formula toolbar
helps you enter worksheet functions. As you enter a function into the formula, the Insert Function dialog box displays
the name of the function, each of its arguments, a description of the function and each argument, the current result of
the function, and the current result of the entire formula.

IF
Returns one value if a condition you specify evaluates to TRUE and another value if it
evaluates to FALSE.
Use IF to conduct conditional tests on values and formulas.
Example 1

Data
50

Formula                              Description (Result)
Enter in C22                         If the number in cell B22 is less than or equal to
=IF(B22<=100,"Within                 100, then the formula displays the text "Within
budget","Over budget")               budget". Otherwise, the function displays "Over
budget" (Within budget). Change the value in
cell B22 to 120 to check the function.

Example 2
Actual Expenses                      Predicted Expenses
1500                                 900
500                                  850
500                                  925

Formula                              Description (Result)
Enter in D28 =IF(B28>C28,"Over       Checks whether the first row is over budget
Budget","OK")                        (Over Budget)
Enter in D29 =IF(C29>850,"Over       Checks whether the second row is over budget
Budget","OK")                        (OK)
amples

ctions

nly used functions
nsert Function      dialog box found on the formula toolbar
on into the formula, the Insert Function dialog box displays
tion of the function and each argument, the current result of

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 158 posted: 7/6/2010 language: English pages: 24
Description: Formula for Calculating Percentages document sample
How are you planning on using Docstoc?