Excel Training – Page 78 – STL

Calculate the percentage if you know the total and amount

For example, if you score 42 points correctly out of 50, what is the percentage of correct answers?

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

A	B
Points answered correctly	Total points possible
42	50
Formula	Description (Result)
=A2/B2	Divides 42 by 50 to find the percentage of correct answers (0.84 or 84%)

Note You can view the number as a percentage. Select the cell, and then on the Home tab, in the Number group, click Percent Style .

Calculate the total if you know the amount and percentage

For example, the sale price of a shirt is $15, which is 25% off the original price. What is the original price? In this example, you want to find 75% of which number equals 15.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

A	B
Sale price	100% minus the discount (in decimal form)
15	0.75
Formula	Description (Result)
=A2/B2	Divides 15 by 0.75 to find the original price (20)

Calculate the difference between two numbers as a percentage

For example, your earnings are $2,342 in November and $2,500 in December. What is the percentage change in your earnings between these two months? To do this task, use the ABS function and the subtraction (-) and division (/) operators.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

A	B
November earnings	December earnings
2342	2500
Formula	Description (Result)
=(B2-A2)/ABS(A2)	Divides the difference between the second and first numbers by the absolute value of the first number to get the percentage change (0.06746 or 6.75%)

Note You can view the number as a percentage. Select the cell, and then on the Home tab, in the Number group, click Percent Style .

Function details

The ABS function returns the absolute value of a number. The absolute value of a number is the number without its sign.

Increase or decrease a number by a percentage

For example, you spend an average of $25 on food each week, and you want to cut your weekly food expenditures by 25%. How much can you spend? Or, if you want to increase your weekly food allowance of $25 by 25%, what is your new weekly allowance?

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2

A	B
Number	Percentage
25	25%
Formula	Description (Result)
=A2*(1-B2)	Decreases 25 by 25% (18.75)
=A2*(1+B2)	Increases 25 by 25% (31.25)
=A2*(1+35%)	Increases 25 by 35% (33.75)

Note When you type a number followed by a percent sign (%), the number is interpreted as a hundredth of its value. For example, 5% is interpreted as .05.

A question that often arises when using AutoFilter with Excel is… how can I sum the visible data only? The solution to this question may not always be apparent but all is not lost as there are many Excel training courses in London that deal with how this can be achieved.

There are 2 options for you to do this.

Option 1

Format the data as a table. To do this go to the Home ribbon and from the Styles group click on ‘Format as Table’. Select the table style you require, then, add the total row from the ‘Table Style Options’ group in the Options ribbon. Now, whenever you filter the data the resulting totals will only be shown for the visible cells.

Option 2

2) Should you not wish to work with the data formatted as a table then, apply a filter first, add the totals you require in the row of blank cells below the visible cells. Use the AutoSum tool for this purpose which will create SUBTOTAL functions calculating the visible cells only. When you clear the filter the totals now shown will be for all the data. Adding a new filter will show the totals for the data that is visible.

The 2^nd option can also be used in Excel versions before 2007.

Since there are so many different things that one can do in Excel, it is extremely important to take advantage of the Excel training courses available in London which cover the most useful tools and functions.