Microsoft Office Tutorials and References

In Depth Information

**APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE**

who know Juan Pablo Gonzalez, he once wrote a macro which would simulate

having MEDIAN in a pivot table. Ask him how he did it.

MID(text,start_num,num_chars) [Category: Text]

MID returns a speciﬁ c number of characters from a text string, starting at the

position you specify, based on the number of characters you specify. Guru Tip:

See an example under LEFT. You might sometimes need to use FIND and/

or LEN to ﬁ nd the starting position or the number of characters. In the ﬁ gure

above, the starting position is calculated using the FIND function. The number

of characters is calculated using LEN to get the length of the part number and

then subtracting the position of the dash. Instead of explicitly calculating the

number of characters in this case you could simply ask for a large number.

Excel will not pad the result with spaces: =MID(F4,FIND("-",F4)+1,50). Also see

page 120

MIDB(text,start_num,num_bytes) [Category: Text]

MIDB returns a speciﬁ c number of characters from a text string, starting at the

position you specify, based on the number of bytes you specify. This function is

for use with double-byte characters.

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

Returns the smallest number in a set of values. Guru Tip: Check out SMALL as

well.

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

Returns the smallest value in the list of arguments. Text and logical values such

as TRUE and FALSE are compared as well as numbers.

MINUTE(serial_number) [Category: Date & Time]

Returns the minutes of a time value. The minute is given as an integer, ranging

from 0 to 59. Guru Tip: If you ask for the minute of 1:30, the answer will be 30. If

you expect to ﬁ nd the total number of minutes, use =TEXT(A2,"[m]") instead.

MINVERSE(array) [Category: Math]

Returns the inverse matrix for the matrix stored in an array.

MIRR(values,ﬁ nance_rate,reinvest_rate) [Category: Financial]

Returns the modiﬁ ed internal rate of return for a series of periodic cash ﬂ ows.

MIRR considers both the cost of the investment and the interest received on

reinvestment of cash.

MMULT(array1,array2) [Category: Math]

Returns the matrix product of two arrays. The result is an array with the same

number of rows as array1 and the same number of columns as array2. Guru

Tip: You can use this to solve simultaneous equations in Excel. For an example,

check out Excel 2007 Miracles Made Easy.