| Math & Trig Functions |
Description |
|---|---|
| ABS | Returns the absolute value of a number. |
| AGGREGATE | Returns one of many different aggregates from a list or database |
| Rounding Functions | |
| CEILING.MATH | Returns a Number Rounded away from Zero to the Nearest Multiple of significance |
| FLOOR.MATH | Rounds a number down toward zero to the nearest multiple of significance |
| INT | Rounds a number down to the nearest integer |
| EVEN | Rounds a decimal number away from zero to the nearest even integer |
| ODD | Rounds a decimal number away from zero to the nearest odd integer |
| ROUND | Rounds a number to a specified number of digits |
| ROUNDDOWN | Always rounds a number down |
| ROUNDUP | Always rounds a number up |
| MROUND | Rounds a value to a desired multiple |
| TRUNC | Truncates a number by removing some or all of the decimal portion of a number |
| MOD | Returns the remainder of a division problem |
| GCD | Greatest Common Divisior (Returns the greatest common divisor of two or more integers) |
| LCM | Least Common Multiple (Returns the least common multiple of integers) |
| POWER | Returns the result of a number raised to a power |
| RANDBETWEEN | Generates a Random number between two specified numbers |
| SIGN | Determines the sign of a number then returns a 1 if the number is positive, a zero (0) if the number is 0 and a -1 if the number is negative |
| SUMSQ | Returns the Sum of the Squares |
| PRODUCT | Multiplies all of the numbers given for arguments |
| SUMPRODUCT | Returns the sum of the multiplications of values in corresponding ranges |
| SQRT | Returns the square root of a number |
| SUBTOTAL | Returns the subtotal of the numbers in a column from a list or database |
| SUM | Sums a series of values |
| SUMIF | Used to sum values provided that they meet a condition that you specify |
| SUMIFS | Used to sum values only if multiple conditions are met |
|
Statistical Functions |
|
| Count Functions | |
| COUNT | Counts the number of cells in a range that contain numbers |
| COUNTBLANK | Counts only those cells in a range that are empty |
| COUNTA | Counts the Number of Nonblank cells |
| COUNTIF | Returns a Number Rounded away from Zero to the Nearest Multiple of significance |
| COUNTIFS | Rounds a number down toward zero to the nearest multiple of significance |
| Frequency and Rank Functions | |
| FREQUENCY | Number of times values appear within a range of values |
| RANK.EQ | Returns the rank of a number in a list of numbers |
| RANK.AVG | Returns the rank of a number in a list of numbers. If more than one value has the same rank, the average rank is returned. |
| PERCENTRANK.INC | Returns the rank of a value in a data set as a percentage (0 to 100 inclusive) of the data set |
| PERCENTRANK.EXC | Returns the rank of a value in a data set as a percentage (0 to 100 exclusive) of the data set |
| PERCENTILE.INC | Given an array of values, it returns the value that is at the percentile you specify. Can find values anywhere from the 0 percentile to the 100-percentile inclusive. |
| PERCENTILE.EXC | Given an array of values, it returns the value that is at the percentile you specify. Can find values anywhere from the 0 percentile to the 100-percentile exclusive. |
| Measures of Central Tendency | |
| AVERAGE | Returns the Average of its arguments from only those cells that contain numeric data |
| AVERAGEA | Returns the Average of its arguments. It includes Numeric and Nonnumeric cells. It does not include Blanks. |
| AVERAGEIF | Averages values that meet a single criterion |
| AVERAGEIFS | Averages values that meet multiple criteria |
| MEDIAN | The median is the the middle number of a group of numbers; half the numbers have values greater than the median, and half the numbers have values less than the median |
| MODE.SNGL | Finds the Mode which is the most frequently occuring number in a set of numbers |
| MODE.MULT | Returns a vertical array of the most frequently occuring, or repetivitive values in an array or range of data. |
| Size Functions | |
| MIN | Returns the smallest value from a set of values |
| MAX | Returns the largest value from a set of values |
| LARGE | Returns the nth largest value from a set of values |
| SMALL | Returns the nth smallest value from a set of values |
| Variance Functions | |
| VAR.S | Estimates variance based on a sample of the entire population |
| VAR.P | Calculates variance based on the entire population |
| Standard Deviation | |
| STDEV.S | Standard deviation based on a sample of the entire population |
| STDEV.P | Standard deviation based on the entire population |
| Trend Line Functions | |
| SLOPE | Measures the rate of Change in a line |
| INTERCEPT | The Point where a lince crosses the Y-axis |
| TREND | Use this function when you have a series of known X and Y coordinates and you want to follow the pattern of that line to new X-coordinates that you specify. Excel will return the y_coordinates for the new X-coordinates |
| FORECAST | Predicts a future value based on existing values |
| Text Functions | |
| CONCATENATE | Used to join as many as 255 text strings into a single text string |
| LEN | Returns the number of characters in a string |
| Alignment Functions | |
| LEFT | Returns the number of characters you specify from the left side of a string. |
| MID | Returns the number of characters you specify from a string starting at the position you specify |
| RIGHT | Returns the number of characters you specify from the right side of a string |
| Change the Case of a String | |
| LOWER | Converts a string to all lowercase letters |
| UPPER | Converts a string to all uppercase letters |
| PROPER | Capitalizes the first letter in a string and all other letters that follow any character other than a letter. All other characters in the string are converted to lowercase letters |
| Find a String within another String | |
| FIND | Returns the starting position of where one string is found in another string. It is case sensitive. |
| SEARCH | Returns the starting position of where one string is found in another string. It is not case sensitive. You can use wildcards in the search string. |
| Replace Part of a String with another String | |
| REPLACE | Replaces part of a text string with a different text string |
| SUBSTITUTE | Replaces existing text with new text. |
|
Lookup & Reference Functions |
|
| ADDRESS | Returns the address of a cell in a worksheet, given specified row and column numbers. |
| AREAS | Returns the number of areas in a reference. The reference can include a single cell, a range of contiguous cells, or a range of non-contiguous cells. |
| CHOOSE | Determines which value from a list of values is to be retrieved based on its position in the list |
| Column and Row Functions | |
| COLUMN | Returns the column number from a reference |
| COLUMNS | Returns the number of columns in an array or reference |
| ROW | Returns the row number of a reference |
| ROWS | Returns the number of rows in an array or reference |
| OFFSET | Returns a reference to a range that is a given number of rows and columns from a given reference |
| TRANSPOSE | Converts a vertical range of cells to a horizontal range, or vice versa |
| Search Functions | |
| INDEX | Used to retrieve the value from a single row or the value from any two-dimensional range. It doesn't use a lookup value it just retrieves the value from the specified location. |
| LOOKUP | Searches a single row or column for a value and then returns a value from the same position in another single row or column range. |
| HLOOKUP | Horizontal Lookup. Uses a lookup value to search the top row of a table or array for a matching value then returns the value from the same column as the row you specify. |
| VLOOKUP | Looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify |
| MATCH | Returns the relative position of an item in an array that matches a specified value in a specified order |