SUM FUNCTION
Description: Adds all the numbers in a range of cells.
Formula: =SUM(number1, [number2], ...)
Example: =SUM(A1:A5) adds all the numbers from cell A1 to A5.
AVERAGE FUNCTION
Description: Calculates the average of the numbers in a range of cells.
Formula: =AVERAGE(number1, [number2], ...)
Example: =AVERAGE(B1:B10) calculates the average of numbers in cells B1 to B10.
COUNT FUNCTION
Description: Counts the number of cells that contain numbers in a range.
Formula: =COUNT(value1, [value2], ...)
Example: =COUNT(C1:C10) counts the number of cells with numbers in cells C1 to C10.
COUNTA FUNCTION
Description: Counts the number of non-empty cells in a range.
Formula: =COUNTA(value1, [value2], ...)
Example: =COUNTA(D1:D10) counts the number of non-empty cells in cells D1 to D10.
MAX FUNCTION
Description: Returns the largest number in a range of cells.
Formula: =MAX(number1, [number2], ...)
Example: =MAX(E1:E10) finds the largest number in cells E1 to E10.
MIN FUNCTION
Description: Returns the smallest number in a range of cells.
Formula: =MIN(number1, [number2], ...)
Example: =MIN(F1:F10) finds the smallest number in cells F1 to F10.
IF FUNCTION
Description: Checks whether a condition is met and returns one value if TRUE, and another value if FALSE.
Formula: =IF(logical_test, value_if_true, value_if_false)
Example: =IF(G1>50, "Pass", "Fail") returns “Pass” if the value in G1 is greater than 50, otherwise it returns “Fail”.
CONCATENATE (or CONCAT) FUNCTION
Description: Joins several text strings into one string.
Formula: =CONCATENATE(text1, [text2], ...) or =CONCAT(text1, [text2], ...)
Example: =CONCATENATE(H1, " ", I1) or =CONCAT(H1, " ", I1) joins the text in H1 and I1 with a space in between.
VLOOKUP FUNCTION
Description: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
Formula:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:=VLOOKUP(J1, A1:D10, 4, FALSE) searches for the value in J1 within the range A1and returns the value in the fourth column of the range.
HLOOKUP FUNCTION
Description: Searches for a value in the first row of a table and returns a value in the same column from a specified row.
Formula:=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example:=HLOOKUP(K1, A1:D10, 4, FALSE) searches for the value in K1 within the range A1and returns the value in the fourth row of the range.
MATCH FUNCTION
Description: Returns the relative position of an item in an array that matches a specified value.
Formula: =MATCH(lookup_value, lookup_array, [match_type])
Example: =MATCH(L1, B1:B10, 0) returns the position of the value in L1 within the range B1
INDEX FUNCTION
Description: Returns the value of an element in a table or array, selected by the row and column number indexes.
Formula:=INDEX(array, row_num, [column_num])
Example:=INDEX(A1:C10, 2, 3) returns the value in the second row and third column of the range A1
LEFT FUNCTION
Description: Returns the specified number of characters from the start of a text string.
Formula: =LEFT(text, [num_chars])
Example: =LEFT(M1, 5) returns the first five characters in the text of cell M1.
RIGHT FUNCTION
Description: Returns the specified number of characters from the end of a text string.
Formula: =RIGHT(text, [num_chars])
Example: =RIGHT(N1, 3) returns the last three characters in the text of cell N1.
MID FUNCTION
Description: Returns a specific number of characters from a text string, starting at the position you specify.
Formula: =MID(text, start_num, num_chars)
Example: =MID(O1, 3, 2) returns two characters from the text in cell O1, starting at the third character.
LEN FUNCTION
Description: Returns the number of characters in a text string.
Formula: =LEN(text)
Example: =LEN(P1) returns the number of characters in the text of cell P1.
TRIM FUNCTION
Description: Removes all spaces from text except for single spaces between words.
Formula: =TRIM(text)
Example: =TRIM(Q1) removes extra spaces from the text in cell Q1.
LOWER FUNCTION
Description: Converts all characters in a text string to lowercase.
Formula: =LOWER(text)
Example: =LOWER(R1) converts the text in cell R1 to lowercase.
UPPER FUNCTION
Description: Converts all characters in a text string to uppercase.
Formula: =UPPER(text)
Example: =UPPER(S1) converts the text in cell S1 to uppercase.
PROPER FUNCTION
Description: Capitalizes the first letter of each word in a text string.
Formula: =PROPER(text)
Example: =PROPER(T1) converts the text in cell T1 to proper case (first letter of each word in uppercase).
TODAY FUNCTION
Description: Returns the current date.
Formula: =TODAY()
Example: =TODAY() returns the current date.
NOW FUNCTION
Description: Returns the current date and time.
Formula: =NOW()
Example: =NOW() returns the current date and time.
DATE FUNCTIONS
Description: Returns the serial number of a particular date.
Formula: =DATE(year, month, day)
Example: =DATE(2024, 7, 11) returns the serial number for July 11, 2024.
TIME FUNCTION
Description: Returns the serial number of a particular time.
Formula: =TIME(hour, minute, second)
Example: =TIME(14, 30, 0) returns the serial number for 2:30:00 PM.
YEAR FUNCTION
Description: Returns the year of a date.
Formula: =YEAR(serial_number)
Example: =YEAR(U1) returns the year of the date in cell U1.
MONTH FUNCTION
Description: Returns the month of a date.
Formula: =MONTH(serial_number)
Example: =MONTH(V1) returns the month of the date in cell V1.
DAY FUNCTIONS
Description: Returns the day of a date.
Formula: =DAY(serial_number)
Example: =DAY(W1) returns the day of the date in cell W1.
HOUR FUNCTION
Description: Returns the hour of a time.
Formula: =HOUR(serial_number)
Example: =HOUR(X1) returns the hour of the time in cell X1.
MINUTE FUNCTION
Description: Returns the minute of a time.
Formula: =MINUTE(serial_number)
Example: =MINUTE(Y1) returns the minute of the time in cell Y1.
SECOND FUNCTION
Description: Returns the second of a time.
Formula: =SECOND(serial_number)
Example: =SECOND(Z1) returns the second of the time in cell Z1.
ROUND FUNCTION
Description: Rounds a number to a specified number of digits.
Formula: =ROUND(number, num_digits)
Example: =ROUND(3.14159, 2) returns 3.14, rounding the number to 2 decimal places.
ROUNDDOWN FUNCTION
Description: Rounds a number down towards zero.
Formula: =ROUNDDOWN(number, num_digits)
Example: =ROUNDDOWN(3.14159, 2) returns 3.14, rounding the number down to 2 decimal places.
ROUNDUP FUNCTION
Description: Rounds a number up, away from zero.
Formula: =ROUNDUP(number, num_digits)
Example: =ROUNDUP(3.14159, 2) returns 3.15, rounding the number up to 2 decimal places.
CEILING FUNCTION
Description: Rounds a number up to the nearest multiple of significance.
Formula: =CEILING(number, significance)
Example: =CEILING(3.14159, 0.1) returns 3.2, rounding the number up to the nearest 0.1.
FLOOR FUNCTION
Description: Rounds a number down to the nearest multiple of significance.
Formula: =FLOOR(number, significance)
Example: =FLOOR(3.14159, 0.1) returns 3.1, rounding the number down to the nearest 0.1.
ABS FUNCTION
Description: Returns the absolute value of a number.
Formula: =ABS(number)
Example: =ABS(-5) returns 5.
SQRT FUNCTIONS
Description: Returns the square root of a number.
Formula: =SQRT(number)
Example: =SQRT(16) returns 4.
POWER FUNCTION
Description: Returns the result of a number raised to a power.
Formula: =POWER(number, power)
Example: =POWER(2, 3) returns 8.
MOD FUNCTION
Description: Returns the remainder after a number is divided by a divisor.
Formula: =MOD(number, divisor)
Example: =MOD(10, 3) returns 1.
SUMIF FUNCTION
Description: Adds the cells specified by a given condition or criteria.
Formula:=SUMIF(range, criteria, [sum_range])
Example:=SUMIF(A1:A10, ">5") adds all numbers in the range A1that are greater than 5.
COUNTIF FUNCTION
Description: Counts the number of cells that meet a specified condition or criteria.
Formula:=COUNTIF(range, criteria)
Example:=COUNTIF(B1:B10, ">=10") counts the number of cells in the range B1that are greater than or equal to 10.
AVERAGEIF FUNCTION
Description: Calculates the average of the cells specified by a given condition or criteria.
Formula:=AVERAGEIF(range, criteria, [average_range])
Example:=AVERAGEIF(C1:C10, "<5") calculates the average of the cells in the range C1that are less than 5.
