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
have just kept using the “ 1/ ” . All of these are covered in Chapter
• 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.