Microsoft Excel Function Index

Microsoft Excel Function Index

The following chart is a list of common Excel functions and operations. This chart is not a complete list of all the functions in Excel's Function Library, but all these functions are useful for MTH 154 and other calculation based classes. The inputs of most functions can be numbers or cell references.

Function Syntax / * + ^

=ABS(number)

Function Usage Divides the values before the symbol by the value after the symbol. Multiplies the values before and after the symbol.

Adds the values before and after the symbol.

Subtracts the values before and after the symbol. Raises the value before the caret to the value after the caret. Takes the absolute value of the number in the parenthesis.

Example Find the quotient of 4 and 2. Input: = 4/2 Output: 2 Find the product of 4 and 2. Input: = 4 2 Output: 8 Find the sum of 4 and 6. Input: = 4 + 6 Output: 10 Find the difference of 2 and 6. Input: = 2 - 6 Output: -4 Find the square of 3. Input: = 3^2 Output: 9 Find the absolute value of -1. Input: = (-1) Output: 1

Provided by

The Academic Center for Excellence

1

Microsoft Excel Function Index October 2018

Function Syntax =SQRT(number)

=ROUNDUP(number, number of decimal places)

=ROUNDDOWN(number, number of decimal places)

=ROUND(number, number of decimal places) =SUM(first number, second number, etc.) OR =SUM(first cell reference: last cell reference)

Function Usage Returns the square root of the number in the parenthesis.

Number is rounded up to the specified number of decimal places.

Number is rounded down to the specified number of decimal places.

Rounds the value in the parenthesis either up or down, depending on the number.

Example Find the square root of 16. Input: = (16) Output: 4 Round 18.567 up to 2 decimal places. Input: = (18.567,2) Output: 18.57 Round 18.567 down to 2 decimal places. Input:

= (18.567,2) Output: 18.56 Round 18.765 to 2 decimal places. Input:= (18.765,2) Output: 18.77

Sums the data listed within the parenthesis.

Find the sum of 1, 1, and 5. Input: = (1,1,5) Output: 7

Provided by

The Academic Center for Excellence

2

Microsoft Excel Function Index October 2018

Function Syntax =AVERAGE(first number, second number, etc.) OR =AVERAGE(first cell reference: last cell reference) =MAX(first number, second number, etc.) OR =MAX(first cell reference: last cell reference) =MIN(first number, second number, etc.) OR =MIN(first cell reference: last cell reference) =MEDIAN(first number, second number, etc.) OR =MEDIAN(first cell reference: last cell reference)

Function Usage

Example

Averages the data listed within the parenthesis.

Find the average of 4, 2, and 6. Input: = (4,2,6) Output: 4

Finds the maximum value of the data listed in the parenthesis.

Find the max value of 1, 5, and 3. Input: = (1,5,3) Output: 5

Finds the minimum value of the data listed within the parenthesis.

Find the min value of 1, 5, and 3. Input: = (1,5,3) Output: 1

Returns the median value of the data inputted in the parenthesis.

Find the median of 1, 2, 3, 4, and 5. Input: = (1,2,3,4,5) Output: 3

Provided by

The Academic Center for Excellence

3

Microsoft Excel Function Index October 2018

Function Syntax =COUNT(first number, second number, etc.) OR =COUNT(first cell reference: last cell reference)

Function Usage

Counts the number of data points in the parenthesis.

=SLOPE(first y cell reference: last y cell reference, first x cell reference: last x cell reference)

Outputs the slope of a linear regression equation given points x and y. Inputs must be in terms of cell references.

=INTERCEPT(first y cell reference: last y cell reference, first x cell reference: last x cell reference)

Outputs the y-intercept of a linear regression equation given points x and y. Inputs must be in terms of cell references.

Example How many values are listed: 1, 3, 4, 5? Input: = (1,3,4,5) Output: 4 Given the points on a line, (2,3) and (6,4), find the slope. Input: First, enter data into a spreadsheet.

Then, type: = (2: 3, 2: 3)) Output: ? Given the points on a line, (2,3) and (6,4), find the intercept. Input: First, enter data into a spreadsheet.

Then, type: = (2: 3, 2: 3)

Output: 2.5

Provided by

The Academic Center for Excellence

4

Microsoft Excel Function Index October 2018

Function Syntax

=IF(logical test,[value if true],[value if false])

=PMT(periodic rate, total number of payments made, - principal)

Function Usage

Example

Create an if statement that will

output "true" if 1 is greater than

Returns one of two values 0 and "false" if 0 is greater than

after a logical test is

1.

completed.

Input:

= (1 > 0, "true","false")

Output: true

Returns the periodic

Find the monthly payment for a

payment for a given loan. 2 year loan of 50000 with an

Because a loan represents APR of 5%.

money owed, the

Input:

principal is written with a negative sign.

= (01.025 , 24, -50000) Output: 2193.57

Provided by

The Academic Center for Excellence

5

Microsoft Excel Function Index October 2018

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download