Common 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)
Adds all the values in a range of cells that meet a specified
07 SUMIF Math & Trig criteria. =SUMIF(D4:D8,”>=1000″,C4:C8)
=SUMIF(range,criteria,[sum_range])
Calculates the average value in a range of cells that meet the
08 AVERAGEIF Statistical specified criteria. =AVERAGEIF(F4:F8,”Yes”,E4:E8)
=AVERAGEIF(range,criteria,[average_range])
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
S/N FUNCTION CATEGORY DESCRIPTION USAGE
Returns True if the supplied value is numeric and False if
1 ISNUMBER Information =ISNUMBER(A3)
it is not numeric
2 RAND Math & Trig Generates a random number between 0 and 1 =RAND()
Rounds off a decimal value to the specified number of
3 ROUND Math & Trig =ROUND(3.14455,2)
decimal points
Returns the number in the middle of the set of given
4 MEDIAN Statistical =MEDIAN(3,4,5,2,5)
numbers
5 PI Math & Trig Returns the value of Math Function PI(π) =PI()
Returns the result of a number raised to a power.
6 POWER Math & Trig =POWER(2,4)
POWER( number, power )
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
FUNCTI
S/N CATEGORY DESCRIPTION USAGE COMMENT
ON
Returns a number of specified characters from the start Left 4 Characters of
1 LEFT Text =LEFT(“GURU99”,4)
(left-hand side) of a string “GURU99”
Returns a number of specified characters from the end Right 2 Characters of
2 RIGHT Text =RIGHT(“GURU99”,2)
(right-hand side) of a string “GURU99”
Retrieves a number of characters from the middle of a string
Retrieving Characters 2 to
3 MID Text from a specified start position and length. =MID(“GURU99”,2,3)
5
=MID (text, start_num, num_chars)
Informatio
4 ISTEXT Returns True if the supplied parameter is Text =ISTEXT(value) value – The value to check.
n
Returns the starting position of a text string within another
Find oo in “Roofing”,
5 FIND Text text string. This function is case-sensitive. =FIND(“oo”,”Roofing”,1)
Result is 2
=FIND(find_text, within_text, [start_num])
REPLAC Replaces part of a string with another specified string. =REPLACE(“Roofing”,2,2,”
6 Text Replace “oo” with “xx”
E =REPLACE (old_text, start_num, num_chars, new_text) xx”)
Date Time Functions
S/N FUNCTION CATEGORY DESCRIPTION USAGE
Returns the number that represents
1 DATE Date & Time =DATE(2015,2,4)
the date in excel code
Find the number of days between
2 DAYS Date & Time =DAYS(D6,C6)
two dates
Returns the month from a date =MONTH(“4/2/20
3 MONTH Date & Time
value 15”)
Returns the minutes from a time =MINUTE(“12:31”
4 MINUTE Date & Time
value )
=YEAR(“04/02/20
5 YEAR Date & Time Returns the year from a date value
15”)
