Microsoft Office Tutorials and References

In Depth Information

**10. Understanding Functions**

more, as the Open Document standard now includes CSC (Cosecant),

SEC (Secant), and COT (Cotangent). Of course, when you add trig func-

tions, you also have to have their hyperbolic equivalent and their

imaginary equivalent. In order to prevent you from using the
“
1/
”
in

1/SIN
”
, you now have these new functions: ACOT, ACOTH, COT, COTH,

CSC, CSCH, IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH,

IMSINH, IMTAN, SEC, and SECH. Maybe it would have been easier to

•
Excel 2010 added the AGGREGATE function, which is similar to

SUBTOTALS but with 19 calculations instead of 11. This function can

ignore other subtotals, error cells, and/or rows hidden manually. The

arguments from 12 through 19 work as array formulas, allowing some

flexibility. See
Chapter 11
.

•
Excel 2007 added the IFERROR function to simplify handling of #N/A

and #DIV/0 errors. See
Chapter 12
.

•
Excel 2007 introduced plural SUMIFS, COUNTIFS, and AVERAGEIFS

functions. The plural versions handle multiple conditions and are

amazingly fast. If you
’
ve ever used SUMPRODUCT because SUMIF

handles only one condition, you will love SUMIFS. See
Chapter 11
.

What are the other new functions? They include the following:

•
New statistical functions, such as BINOM.DIST.RANGE, COMBIN,

COMBINA, GAMMA, GAUSS, PERMUTATIONA, and PHI.

•
The old CHAR and CODE are extended with UNICHAR and UNICODE to

return extended characters beyond CHAR(255).

•
New financial functions include PDURATION and RRI.

•
New date functions are DAYS and ISOWEEKNUM, and NUMBERVALUE

enables you to convert text to numbers, even text that has the

“
wrong
”
decimal character.

•
SHEET and SHEETS tell you the worksheet number of a cell referen-

ce and how many sheets are in a 3D reference.

•
MUNIT returns the correct Identity matrix, eliminating the chance of

any floating-point errors creeping in when you use

MMULT(Matrix,MINVERSE) to generate the identity matrix.