Calculating a percentage of a total

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which formula finds the share of the total for the value in B2, when the total is in B10?

A =B2*B10

B =B2/$B$10

2. You copy =B2/B10 down the column. Row 3 becomes =B3/B11, which is blank. What is the fix?

A Type the formula again in every row without a $

B Lock the total with $: =B2/$B$10

3. A cell shows 0.25 after you divide a part by the total. How do you show it as 25%?

A Click the Percent style button

B Multiply the formula by 25

4. If you work out each item's share of the total correctly, what should all the shares add up to?

A 100%

B 10%

5. A student uses =B2/B10 and then formats the cell as a percentage. In a share-of-total formula, the total is...

A The number you divide by

B The number you divide

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=B2/$B$10		AThe formula is upside down. It divides the total by the part, giving a number bigger than 1. It should be part divided by total: =B2/$B$10.
2$B$10		B0.25 becomes 25%
3To find each item's share of the total, divide the part by the total and lock the total cell		C=B3/$B$10
4After dividing, click the Percent style button (%) on the Home tab so the decimal shows as a percentage		DA locked reference to the total, so it stays the same when the formula is copied down the column
5=B2/B10 (then copied down to B3, B4...)		EThe total B10 is not locked with $, so when the formula is copied down it becomes B11, B12... and divides by the wrong cells or gives errors. It should be =B2/$B$10.
6=$B$10/B2		FDivides one part (B2) by the total in B10 to work out its share of the whole

Definitions

=B2/$B$10

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=B2_$B$10

=B2/_____

=___(B2:B9)

=B2/$B$10 =B3/$B$__

=__/$B$10 =B3/$B$10

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A A10: Total

B B10: =SUM(B2:B9)

C C2: =B2*$B$10

D C3: =B3/$B$10

Question 2

A B10: =SUM(B2:B9)

B C2: =B2/$B$10

C C3: =B3/$B$10

D C4: =B4/$B$9

Question 3

A B10: =SUM(B2:B9)

B C2: =B2/$B$10

C C3: =B2/$B$10

D C4: =B4/$B$10

Question 4

A C2: =$B$10/B2

B C3: =B3/$B$10

C C4: =B4/$B$10

Question 5

A B10: =SUM(B2:B9)

B C2: B2/$B$10

C C3: =B3/$B$10

Answer Key

Calculating a percentage of a total

Section A: Quick Questions

B — =B2/$B$10

B — Lock the total with $: =B2/$B$10

A — Click the Percent style button

A — 100%

A — The number you divide by

Section B: Match the Pairs

=B2/$B$10 → Divides one part (B2) by the total in B10 to work out its share of the whole

$B$10 → A locked reference to the total, so it stays the same when the formula is copied down the column

To find each item's share of the total, divide the part by the total and lock the total cell → =B3/$B$10

After dividing, click the Percent style button (%) on the Home tab so the decimal shows as a percentage → 0.25 becomes 25%

=B2/B10 (then copied down to B3, B4...) → The total B10 is not locked with $, so when the formula is copied down it becomes B11, B12... and divides by the wrong cells or gives errors. It should be =B2/$B$10.

=$B$10/B2 → The formula is upside down. It divides the total by the part, giving a number bigger than 1. It should be part divided by total: =B2/$B$10.

Section C: Definitions

=B2/$B$10 — Divides a part (B2) by the total ($B$10, locked with $) to find its share. Format the cell with the Percent style button to show it as a %. All the shares add up to 100%.

Section D: Fill in the Blanks

=B2/$B$10

=SUM(B2:B9)

=B2/$B$10
=B3/$B$10

=B2/$B$10
=B3/$B$10

Section E: Spot the Error

C — A share is found by dividing the part by the total, not multiplying. It should be =B2/$B$10.

D — $B$9 is just one of the parts, not the total. Every share must divide by the locked total, so it should be =B4/$B$10.

C — Row 3's share must use its own part, B3. Using B2 would just repeat the share for row 2. It should be =B3/$B$10.

A — The part goes on top and the total underneath. The formula is upside down and should be =B2/$B$10.

B — Every formula must start with =. Without it, the spreadsheet treats it as text. It should be =B2/$B$10.