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”)
0
Would love your thoughts, please comment.x
()
x