Part II: Working with Formulas and Functions
out any decimals that may exist. Finally, it wraps that result in the LEN function to get a
measure of how many digits are in the number without any decimals or negation symbols.In the example, this part of the formula results in 4. If you take the number –2330.45 and
strip away the decimals and negative symbol, you have four digits left.This number is then multiplied by –1 to make it a negative number, and it is then added to
the number of significant digits we are seeking. In this example, 4*-1+2 = –2.Again, this formula will be used as the second argument for our ROUND function. Enter this
formula into Excel, and you’ll round this number to –2300 (two significant digits).=ROUND(-2330.45,LEN(INT(ABS(-2330.45)))*-1+2)You can then replace this formula with cell references that point to the source number and
cell that holds the number of desired significant digits. This what you saw in Figure 10.11.
=ROUND(B5,LEN(INT(ABS(B5)))*-1+$E$3)Counting Values in a Range
Excel provides several functions to count the values in a range: COUNT, COUNTA, and
COUNTBLANK. Each of these functions provides a different method of counting based on
whether the values are numbers, numbers and text, or blank.Figure 10.12 illustrates the different kinds of counting that you can perform. In row 12, we
are using the COUNT function to count only the exams where students have passed. In col-
umn H, we are using the COUNTA function to count all of the exams taken by a student. In
column I, we are using the COUNTBLANK function to count only those exams that have not
yet been taken.FIGURE 10.12
A demonstration of counting cells