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.

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.

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.

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.

FREQUENCY function to identify the unique values. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a 0 (zero).

IF function to assign a value of 1 to each true condition.

The SUM function to add the unique values.

Tip To see a function evaluated step by step, select the cell containing the formula, and then on the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

Example

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

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.

Let's say you want to find out how many inventory items are not profitable (total inventory – profitable items) or how many employees are approaching retirement age (total employees – employees under 55). There are several ways to subtract numbers.

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.

Let's say you want to figure out how much bottled water that you need for a customer conference (total attendees ׳ 4 days ׳ 3 bottles per day) or the reimbursement travel cost for a business trip (total miles ׳ .46). There are several ways to multiply numbers.

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.

Let's say you want to find out how many person hours it took to finish a project (total project hours ק total people on project) or the actual miles per gallon rate for your recent cross-country trip (total miles ק total gallons). There are several ways divide numbers.

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.

Let's say you want to calculate a sales tax for different states, compute a grade for a test score, or determine a percent change in sales between two fiscal quarters. There are several ways to calculate percentages.

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

Purchase price

Sales tax (in decimal form)

800

0.089

Formula

Description (Result)

=A2*B2

Multiplies 800 by 0.089 to find the amount of sales tax to pay ($71.20)

Note To convert a number in percentage format to a decimal, divide it by 100. For example, the sales tax in this example (8.9) divided by 100 is .089.

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.

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.

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.

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.

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.

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.75)

=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.

Let's say you want to find out who has the the smallest error rate in a production run at a factory or the largest salary in your department. There are several ways to calculate the smallest or largest number in a range.

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.

Let's say you have six bells, each with a different tone, and you want to find the number of unique sequences in which each bell can be rung once. In this example, you are calculating the factorial of six. In general, use a factorial to count the number of ways in which a group of distinct items can be arranged (also called permutations). To calculate the factorial of a number, use the FACT function.

Example

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

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.

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

3

A

1

1

=A1*A2

Enter a row of values from B3 to the right, for example, 1 through 10.

Enter a column of values from A4 down, for example, 1 through 10.

Select all cells in the range except cells A1 and A2.

On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.

In the Row input cell box, enter A1.

In the Column input cell box, enter A2.

Click OK.

Optionally, you can freeze the data table by converting the result to their values.

Let's say you want to round a number to the nearest whole number because decimal values are not significant to you or round a number to multiples of 10 to simplify an approximation of amounts. There are several ways to round a number.

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

3

4

A

Data

20.3

-5.9

12.5493

Formula

Description (Result)

=ROUNDUP(A2,0)

Rounds 20.3 up to the nearest whole number (21)

=ROUNDUP(A3,0)

Rounds -5.9 up (-6)

=ROUNDUP(A4,2)

Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)

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

3

4

A

Data

20.3

-5.9

12.5493

Formula

Description (Result)

=ROUNDDOWN(A2,0)

Rounds 20.3 down to the nearest whole number ( 20)

=ROUNDDOWN(A3,0)

Rounds -5.9 down (-5)

=ROUNDDOWN(A4,2)

Rounds the number down to the nearest hundredth, two decimal places ( 12.54)

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.

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

3

A

Data

1.25

30.452

Formula

Description (Result)

=ROUND(A2,1)

Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)

=ROUND(A3,2)

Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)

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.

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.

Let's say you want to calculate an extremely small tolerance level for a machined part or the vast distance between two galaxies. To raise a number to a power, use the "^" operator or the POWER function.

Example

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

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.