Mad Marv’s Place

I’m reposting some of my articles I wrote during a spreadsheet education project on The Motley Fool. The complete thread can be found here (Guest registration required for non-members):

Arithmatic

=SUM(A1:A6)           Adds the contents of cells A1 thru A6.

=SUM(A1:A3,-A5)     Adds the contents of cells A1, A2, A3 & subtracts A5.

=PRODUCT(A1:A6)   Multiplies the contents of A1 thru A6.

=PRODUCT(A1:B3)   Multiplies the contents of A1, A2, A3, B1, B2, & B3.

=QUOTIENT(A1,A2)  Divides A1 by A2.

=POWER(A1,A2)      Raises A1 to the A2 power.

=SQRT(A1)              Takes the square root of A1.

Irrational numbers. Excel also has some special functions for common irrational numbers.

=PI()                   pi (3.14...), returns pi accurately to 15 digits

=EXP(power)        e  (2.71...), returns e raised to the specified power

Trigonometry functions. Note that the inputs for all trig functions must be in radians, not degrees. Excel has functions that can convert degrees to radians and vice versa.

=SIN(angle)           sine

=COS(angle)          cosine

=TAN(angle)          tangent

=ASIN(number)     arcsine

=ACOS(number)    arccosine

=ATAN(number)    arctangent

=RADIANS(angle)  degrees -> radians

=DEGREES(angle)  radians -> degrees

Hyperbolic trig functions are similar, just add an H to the end of the function name.

Rounding. Excel has an large selection of rounding functions.

=ROUND(number,decimals)      Rounds a number to the specified number of decimals.
                                              Negative decimal places are possible if you want to round
                                              to the nearest 10, 100, etc.

=ROUNDUP(number,decimals)    Rounds up instead of to the nearest decimal.

=ROUNDDOWN(number,decimals)  Rounds down instead of to the nearest decimal.

=INT(number)                   Rounds down to the nearest integer.

=EVEN(number)                Rounds up to the nearest even integer.  If a negative number
                                       is entered, it rounds away from 0.

=ODD(number)                 Rounds up to the nearest even integer.  If a negative number
                                       is entered, it rounds away from 0.

=MROUND(number,multiple)     Rounds to the desired multiple.

=TRUNC(number,decimals)      Cuts off or truncates all decimal places after the specified number

Other functions

=FACT(number)        Factorial (example:  =FACT(5) is 5 * 4 * 3 * 2 * 1)

=ABS(number)          Returns the absolute value of a number

=LOG(number,base)  Returns the logarithm of a number to a specified base

=LN(number)            Returns the natural log (i.e. to the base e) of a number

=LOG10(number)      Returns the log to the base 10 of a number

Tags: Investing //