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.
Returns the modiﬁ ed duration for a security with an assumed par value of
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