Microsoft Office Tutorials and References

In Depth Information

**APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE**

use the result of the MATCH as the row and/or column argument in an INDEX

function. In the image below, a horizontal MATCH in E4 does a lookup to ﬁ nd

which column has the selected product. A vertical match in E5 does a lookup

to ﬁ nd which row has the selected city. The INDEX function in E6 grabs the

appropriate value at the intersection of the selected row and column.

If you have to do a dozen columns of VLOOKUP, you might consider replacing that

with a single MATCH column and then 12 columns of INDEX. While VLOOKUP

and MATCH take a long time to calculate, the INDEX function calculates very

rapidly. Also see page 2.

MAX(number1,number2,...) [Category: Statistical]]

Returns the largest value in a set of values. Guru Tip: You use MAX all the time

to ﬁ nd the largest value in a list. Also check out LARGE which can ﬁ nd the 2nd,

3rd, and 4th largest values.

MAXA(value1,value2,...) [Category: Statistical]]

Returns the largest value in a list of arguments. Text and logical values such as

TRUE and FALSE are compared as well as numbers.

MDETERM(array) [Category: Math]

Returns the matrix determinant of an array.

MDURATION(settlement,maturity,coupon,yld,frequency,basis) [Category:

Financial]*

Returns the modiﬁ ed duration for a security with an assumed par value of

$100.

MEDIAN(number1,number2, ...) [Category: Statistical]]

Returns the median of the given numbers. The median is the number in the

middle of a set of numbers; that is, half the numbers have values that are greater

than the median, and half have values that are less. Guru Tip: For those of you