Excel Functions
Functions
Excel has many premade formulas, called functions.
Functions are typed by = and the functions name.
For example =SUM
Once you have typed the function name you need to apply it to a range.
For example =SUM(A1:A5)
The range is always inside of parentheses.
| Function | Description |
|---|---|
| =AND | Returns TRUE or FALSE based on two or more conditions |
| =AVERAGE | Calculates the average (arithmetic mean) |
| =AVERAGEIF | Calculates the average of a range based on a TRUE or FALSE condition |
| =AVERAGEIFS | Calculates the average of a range based on one or more TRUE/FALSE conditions |
| =CONCAT | Links together the content of multiple cells |
| =COUNT | Counts cells with numbers in a range |
| =COUNTA | Counts all cells in a range that has values, both numbers and letters |
| =COUNTBLANK | Counts blank cells in a range |
| =COUNTIF | Counts cells as specified |
| =COUNTIFS | Counts cells in a range based on one or more TRUE or FALSE condition |
| =DATEDIF | Calculates the number of years, months or days between two dates |
| =FILTER | Returns the rows of a range that meet one or more conditions |
| =HLOOKUP | Allows horizontal searches for values in a table |
| =IF | Returns values based on a TRUE or FALSE condition |
| =IFERROR | Returns a value you choose if a formula gives an error |
| =IFS | Returns values based on one or more TRUE or FALSE conditions |
| =INDEX | Returns the value at a given row and column position in a range |
| =INDEX MATCH | Combines INDEX and MATCH to look up values in any direction |
| =LEFT | Returns values from the left side of a cell |
| =LEN | Returns the number of characters in a cell, including spaces |
| =LOWER | Reformats content to lowercase |
| =MATCH | Returns the position of a value in a range |
| =MAX | Returns the highest value in a range |
| =MEDIAN | Returns the middle value in the data |
| =MID | Returns characters from the middle of a text, from a start position |
| =MIN | Returns the lowest value in a range |
| =MODE | Finds the number seen most times. The function always returns a single number |
| =NETWORKDAYS | Counts the working days (Monday to Friday) between two dates |
| =NPV | The NPV function is used to calculate the Net Present Value (NPV) |
| =OR | Returns TRUE or FALSE based on two or more conditions |
| =PROPER | Capitalizes the first letter of each word |
| =RAND | Generates a random number |
| =RIGHT | Returns values from the right side of a cell |
| =ROUND | Rounds a number to a chosen number of digits |
| =SORT | Returns a range sorted by one of its columns |
| =STDEV.P | Calculates the Standard Deviation (Std) for the entire population |
| =STDEV.S | Calculates the Standard Deviation (Std) for a sample |
| =SUBSTITUTE | Replaces text in a cell with new text |
| =SUM | Adds together numbers in a range |
| =SUMIF | Calculates the sum of values in a range based on a TRUE or FALSE condition |
| =SUMIFS | Calculates the sum of a range based on one or more TRUE or FALSE condition |
| =SUMPRODUCT | Multiplies ranges item by item and returns the sum |
| =TEXTJOIN | Joins text from several cells with a delimiter |
| =TODAY | Returns the current date |
| =TRIM | Removes irregular spacing, leaving one space between each value |
| =UNIQUE | Returns the unique values of a range |
| =UPPER | Reformats content to uppercase |
| =VLOOKUP | Allows vertical searches for values in a table |
| =XLOOKUP | Searches a range and returns the matching value from another range, in any direction |
| =XOR | Returns TRUE or FALSE based on two or more conditions |
