Microsoft Office Tutorials and References

In Depth Information

**BUILT-IN FUNCTIONS**

To achieve this output, cell C17 was used as the value to be rounded. Recall from Figure E-21 that cell

C17 had the formula =AVERAGE(G2:G14). The following ROUND formula would produce the same output in

cell G17: =ROUND(AVERAGE(G2:G14),2). In this case, Excel evaluates the formula

First, the

AVERAGE function is evaluated, yielding the average with many digits. That value is then input to the

ROUND function and rounded to two decimal places.

The ROUNDUP function works much like the ROUND function. ROUNDUP

“

inside out.

”

s output is always rounded

up to the next value. For example, the value 4 would appear in a cell that contained the following formula:

=ROUNDUP(3.12,0). In Figure E-22, if the formula in cell G17 had been =ROUNDUP(AVERAGE(G2:G14),2),

the value 96 would have been the result. In other words, 95.54 rounded up with no decimal places

becomes 96.

’

COUNTIF Function

The COUNTIF function counts the number of values in a range that meet a specified condition. The syntax is:

COUNTIF(range of data, condition)

The condition is a logical expression such as

“

=1

”

,

“

>6

”

,or

“

=F

”

. The condition is shown with quotation

marks, even if a number is involved.

Assume that you want to see the number of female runners in cell C18. Figure E-23 shows the formula

used.

FIGURE E-23

COUNTIF function used in cell C18

The logic of the formula is: Count the number of times that

appears in the data range E2:E14.

As another example of using COUNTIF, assume that column H shows the rounded ratio of the runner

“

F

”

’

s

height in inches to the runner’s time in minutes (see Figure E-24).