Microsoft Office Tutorials and References

In Depth Information

**APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE**

MOD(number,divisor) [Category: Math]

Returns the remainder after number is divided by divisor. The result has the

same sign as divisor. Guru Tip: Remember back to when you ﬁ rst started

to learn division? 22 divided by 4 would be listed as 5 R 2? This function

returns the remainder. You could use this to count off by 3’s (as in gym class):

=MOD(ROW(),3) will classify a data set into groups numbered 0, 1, and 2. Guru

Dave Goodman suggests using =MOD(ROW(),2) as the formula in conditional

formatting to highlight every other row. Also see QUOTIENT. Also see page 11

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

Returns the most frequently occurring, or repetitive, value in an array or range

of data. Like MEDIAN, MODE is a location measure.

MONTH(serial_number) [Category: Date & Time]

Returns the month of a date represented by a serial number. The month is given

as an integer, ranging from 1 (January) to 12 (December).

MROUND(number,multiple) [Category: Math]*

Returns a number rounded to the desired multiple. Guru Tip: If you need to

round a price to the nearest nickel, use MROUND. If you price calculation is

=C2/0.45, then use =MROUND(C2/0.45,0.05).

MULTINOMIAL(number1,number2, ...) [Category: Math]*

Returns the ratio of the factorial of a sum of values to the product of factorials.

N(value) [Category: Information]

Returns a value converted to a number. Guru Tip: If value is numeric, then N

returns the value. If value is text, then N returns zero. I’ve seen people use

this to add a comment to a formula. At the end of the formula, type +N("this is

how the formula is working….."). Since the argument is text, N adds zero to the

formula.

NA( ) [Category: Information]

Returns the error value #N/A. #N/A is the error value that means "no value is

available." Use NA to mark empty cells. By entering #N/A in cells where you

are missing information, you can avoid the problem of unintentionally including

empty cells in your calculations. (When a formula refers to a cell containing #N/

A, the formula returns the #N/A error value.)

NEGBINOMDIST(number_f,number_s,probability_s) [Category:

Statistical]]

Returns the negative binomial distribution. NEGBINOMDIST returns the

probability that there will be number_f failures before the number_s-th success,

when the constant probability of a success is probability_s. This function is