What is a function? (Function Wizard)
A function is a predefined formula. Functions allow you to use descriptive names to automatically apply formulas for you. Examples of functions include;
- SUM for summation of a range of numbers
- AVERAGE for calculating the average of a given range of numbers
- COUNT for counting the number of items in a given range
The importance of functions
Functions increase user productivity when working with excel. Let’s say you would like to get the grand total for the above home supplies budget. To make it simpler, you can use a formula to get the grand total. Using a formula, you would have to reference the cells E4 through to E8 one by one. You would have to use the following formula.
= E4 + E5 + E6 + E7 + E8
With a function, you would write the above formula as
=SUM (E4:E8)
As you can see from the above function used to get the sum of a range of cells, it is much more efficient to use a function to get the sum than using the formula which will have to reference a lot of cells.
Common functions
Let’s look at some of the most commonly used functions in Excel. We will start with statistical functions.
| S/N | FUNCTION | CATEGORY | DESCRIPTION | USAGE |
| 01 | SUM | Math & Trig | Adds all the values in a range of cells | =SUM(E4:E8) |
| 02 | MIN | Statistical | Finds the minimum value in a range of cells | =MIN(E4:E8) |
| 03 | MAX | Statistical | Finds the maximum value in a range of cells | =MAX(E4:E8) |
| 04 | AVERAGE | Statistical | Calculates the average value in a range of cells | =AVERAGE(E4:E8) |
| 05 | COUNT | Statistical | Counts the number of cells in a range of cells | =COUNT(E4:E8) |
| 06 | LEN | Text | Returns the number of characters in a string text | =LEN(B7) |
| 07 | SUMIF | Math & Trig | Adds all the values in a range of cells that meet a specified criteria. =SUMIF(range,criteria,[sum_range]) | =SUMIF(D4:D8,”>=1000″,C4:C8) |
| 08 | AVERAGEIF | Statistical | Calculates the average value in a range of cells that meet the specified criteria. =AVERAGEIF(range,criteria,[average_range]) | =AVERAGEIF(F4:F8,”Yes”,E4:E8) |
| 09 | DAYS | Date & Time | Returns the number of days between two dates | =DAYS(D4,C4) |
| 10 | NOW | Date & Time | Returns the current system date and time | =NOW() |
Numeric Functions
As the name suggests, these functions operate on numeric data. The following table shows some of the common numeric functions.
| S/N | FUNCTION | CATEGORY | DESCRIPTION | USAGE |
| 1 | ISNUMBER | Information | Returns True if the supplied value is numeric and False if it is not numeric | =ISNUMBER(A3) |
| 2 | RAND | Math & Trig | Generates a random number between 0 and 1 | =RAND() |
| 3 | ROUND | Math & Trig | Rounds off a decimal value to the specified number of decimal points | =ROUND(3.14455,2) |
| 4 | MEDIAN | Statistical | Returns the number in the middle of the set of given numbers | =MEDIAN(3,4,5,2,5) |
| 5 | PI | Math & Trig | Returns the value of Math Function PI(π) | =PI() |
| 6 | POWER | Math & Trig | Returns the result of a number raised to a power. POWER( number, power ) | =POWER(2,4) |
| 7 | MOD | Math & Trig | Returns the Remainder when you divide two numbers | =MOD(10,3) |
| 8 | ROMAN | Math & Trig | Converts a number to roman numerals | =ROMAN(1984) |
String functions
These functions are used to manipulate text data. The following table shows some of the common string functions.
| S/N | FUNCTION | CATEGORY | DESCRIPTION | USAGE | COMMENT |
| 1 | LEFT | Text | Returns a number of specified characters from the start (left-hand side) of a string | =LEFT(“GURU99”,4) | Left 4 Characters of “GURU99” |
| 2 | RIGHT | Text | Returns a number of specified characters from the end (right-hand side) of a string | =RIGHT(“GURU99”,2) | Right 2 Characters of “GURU99” |
| 3 | MID | Text | Retrieves a number of characters from the middle of a string from a specified start position and length. =MID (text, start_num, num_chars) | =MID(“GURU99”,2,3) | Retrieving Characters 2 to 5 |
| 4 | ISTEXT | Information | Returns True if the supplied parameter is Text | =ISTEXT(value) | value – The value to check. |
| 5 | FIND | Text | Returns the starting position of a text string within another text string. This function is case-sensitive. =FIND(find_text, within_text, [start_num]) | =FIND(“oo”,”Roofing”,1) | Find oo in “Roofing”, Result is 2 |
| 6 | REPLACE | Text | Replaces part of a string with another specified string. =REPLACE (old_text, start_num, num_chars, new_text) | =REPLACE(“Roofing”,2,2,”xx”) | Replace “oo” with “xx” |
Date Time Functions
These functions are used to manipulate date values. The following table shows some of the common date functions
| S/N | FUNCTION | CATEGORY | DESCRIPTION | USAGE |
| 1 | DATE | Date & Time | Returns the number that represents the date in excel code | =DATE(2015,2,4) |
| 2 | DAYS | Date & Time | Find the number of days between two dates | =DAYS(D6,C6) |
| 3 | MONTH | Date & Time | Returns the month from a date value | =MONTH(“4/2/2015”) |
| 4 | MINUTE | Date & Time | Returns the minutes from a time value | =MINUTE(“12:31”) |
| 5 | YEAR | Date & Time | Returns the year from a date value | =YEAR(“04/02/2015”) |