Analyzing Information using ICT

Analyzing Information using ICT

Selected Math Functions in Excel

One of the many built-in functions provided by Excel that is useful for entrepreneurs at your age is the functions under the category of Math.

The Math functions in Excel perform many of the common mathematical calculations, including basic arithmetic, conditional sums & products, and the trigonometric ratios. However, only few of them will be tackled here in this lesson. The selected functions are those that cover basic and advanced mathematical operations, conditional sums and rounding. These functions are presented in the table below.

Function Description Syntax

SUM Adds all the numbers in a range of cells SUM(numberl, number2,...)

PRODUCT Multiplies all the numbers given as arguments PRODUCT(numberl, number2,...)

QUOTIENT Returns the integer portion of a division QUOTIEIMT(numerator, denominator)

POWER Returns the result of a number raised to a power POWER(number, power)

MOD Returns the remainder after MOD(number, divisor) a number is divided by a divisor

SUMPRODUCT Returns the sum of the SUMPRODUCT(arrayl, array2, ...) products of corresponding ranges or arrays

SUMSQ Returns the sum of the SUMSQ(numberl, number2, ...) squares of the arguments

SUMIF Adds the cells specified by a SUMIFS(range, criteria, sum_range) given condition or criteria

SUMIFS Adds the cells specified by a SUMIF(sum_range, criteria_range, given set of conditions or criteria) criteria

ROUND ' Rounds a number to a ROUND(number, num_digits) specified number of digits

SUM Function:

• To get the sum of the values in column X of the table, you type the formula below and you will get 36

=SUM(A2:A6)

• To get the sum of the values in column X and column Z, you type the formula below and you will get 65

=SUM(A2:A6,C2:C6)

To get the sum of the values in the table, you type the formula below and you will get 102

=SUM(A2:C6)

PRODUCT Function

• To get the product of the values in column X of the table, you type the formula below and you will get 16800

=PRODUCT(A2:A6)

• To get the product of the values in the first row of the table, you type the formula below and you will get 240

=PRODUCT (A2: C2)

• To get the product of the values in the first and third row of the table, you type the formula below and you will get 190080

=SUM(A2:C2,A4:C4)

QUOTIENT Function:

• To get the quotient of the value in the first cell of the table as the numerator and 2 as the denominator, you type the formula below and you will get 6

=QUOTIENT(A2,2)

• To get the quotient of the value in the first cell of the table as the numerator and the second cell to the right as the denominator, you type the formula below and you will get 1

=QUOTIENT(A2,B2)

POWER Function:

• To get the square of the value in the first cell of the table, you type the formula below and you will get 144

=POWER(A2,2)

MOD Function:

• To get the remainder of the value in the first cell of the table and 2 as the divisor, you type the formula below and you will get 0

=MOD(A2,2)

• To get the remainder of the value in the first cell of the table and the second cell to the right as the divisor, you type the formula below and you will get 2

=MOD(A2,B2)

SUMPRODUCT Function

• If you want to multiply the values in column X and column Y of the table and then add the products, you type the formula below and you will get 298

=SUMPRODUCT(A2:A6,B2:B6)

SUMSQ Function

• If you want to get the sum of the squares of the values in column

X, you type the formula below and you will get 307

=SUMSQ(A2:A6)

SUMIF Function

• If you want to add only the values in column X that are larger than

5, you type the formula below and you will get 27

=SUMIF(A2:A6, ">5")

• If you want to add only the values in column Z, where the corresponding cells in column X is larger than 5, you type the formula below and you will get 19

=SUMIF(A2:A6, ">5", C2:C6)

SUMIFS Function

• If you want to add the values in column Z only if two conditions are met:

o The corresponding cells in column X is larger than 5, and

o The corresponding cells in column Y is smaller than 5. then you type the formula below and you will get 0

=SUMIFS(C2:C6, A2:A6, ">5", B2:B6, "<5")

• If you want to add the values in column Z only if two conditions are met:

o The corresponding cells in column X is smaller than 10, and

o The corresponding cells in column Y is larger than 5. then you type the formula below and you will get 22

=SUMIFS(C2:C6, A2:A6, "<10", B2:B6, ">5")

ROUND Function

• After dividing the value in the first cell of the table by the value in the second cell to the right of the table, you want to round the result to two decimal places, you type the formula below and you will get 1.2

=ROUND(A2/B2, 2)

• After dividing 5 by 6 and you want to round the result to two decimal places, you type the formula below and you will get 0.83

=ROUND(5/6, 2)

• To round 0.6489 to two decimal places, you type the formula below and you will get 0.65

=ROUND(0.6489, 2)

Selected Text and Logical Functions in 

Microsoft Excel

Function Description Syntax

CONCATENATE Joins several text strings into one text string CONCATENATE(text1, text2,...)

LEN Returns the number of characters in a text string LEN (text)

LOWER Converts all letters in a text string to lowercase LOWER(text)

UPPER Converts all letters in a text string to uppercase UPPER(text)

LEFT Returns the leftmost characters from a text value LEFT(text, num_chars)

RIGHT Returns the rightmost characters from a text value RIGHT(text, num_chars)

AND Returns TRUE if all of its arguments are TRUE AND(logical1, logical2, ...)

IF Checks whether a condition is met and returns one value if TRUE, otherwise returns another value IF(logical_test, value_if_true, value_if_false)

OR Returns TRUE if any argument is TRUE OR(logicall, logical2, ...)

CONCATENATE Function

• If you want to create a sentence by joining the data in the cells with other text, then type the formula below and you will get

The teacher of Xavier is A' for the first cell.

=CONCATENATE("The teacher of ",A2, " is ”,B2)

LEN Function

• If you want to find the number of characters in a cell, then type the given formula and you will get 6.

=LEN(A2)

LOWER Function

• If you want to change the text to all lowercase, then type the formula below and you will get "xavier".

=LOWER(A2)

UPPER Function

• If you want to change the text to all uppercase, then type the formula below and you will get "XAVIER".

=UPPER(A2)

LEFT Function

• If you want to get the first character of the string in a cell, then type the formula below and you will get "X".

=LEFT(A2)

• If you want to get the first two characters of the string in a cell, then type the formula below and you will get "Xa".

=LEFT(A2,2)

RIGHT Function

• If you want to get the first character to the right of the string in a cell, then type the formula below and you will get "r".

=RIGHT(A2)

• If you want to get the first two characters to the right of the string in a cell, then type the formula below and you will get "er".

=RIGHT(A2,2)

IF Function

• If you want to return one value if a condition is true and another value if it's false, then try the following:

=IF(C2>35,"Very Good","Good") Result: Very Good

=IF(C2>40,"Excellent", IF(C2>35,"Very Good","Good")) Result: Excellent

AND Function

• If you want to evaluate whether the arguments are true, then type the following:

=AND(2+2=4, 2+3=5) Result: TRUE

=AND(1<C2, C2<100) Result: TRUE

=IF(AND(1<C2,C2<100), A2, "The value is out of range.") Result: Xavier

OR Function

• If you want to evaluate if any of the argument is true, then type the following:

=OR(2+2=4, 2+3=5) Result: TRUE

=OR(50<C2, C2<100) Result: TRUE

=IF(C)R(50<C2,C2>100),A2, "The value is out of range.") Result: The value is out of range.

Selected Statistical Functions in

Microsoft Excel

Function Description Syntax 1

AVERAGE Returns the average of its arguments AVERAGE(numberl,number2,...)

AVERAGEIF Finds the average of all the cells in a range that meet a AVERAGE( range, criteria, average_range)

COUNT given criteria Counts how many numbers are in the list of arguments COUNT(valuel,value2,...)

COUNTA Counts how many values are in the list of arguments COUNTA(valuel,value2,...)

COUNTIF Counts the number of cells within a range that meet the given criteria COUNTIF(range,criteria)

MAX Returns the maximum value in a list of arguments MAX(numberl,number2,...)

MIN Returns the minimum value in a list of arguments MIN(numberl,number2,...)

MEDIAN Returns the median of the given numbers MEDIAN(numberl,number2,...)

MODE.SNGL Returns the most common value in a data set MODE.SNGL(numberl,number2,...)

RANK.AVG Returns the rank of a number in a list of numbers; its size relative to other values in the list; if more than one value has the same rank, the average rank is returned

AVERAGE Function

• If you want to get the Average of the three scores, then type the formula below and you will get 30.

=AVERAGE(B3: D3)

AVERAGEIF Function

• If you want to get the Average of all the scores less than 30, then type the formula below and you will get 23.91

=AVERAGEIF(B3:D17,"<30")

• If you want to get the Average of all the scores in Exam2 with an Example greater than 40, then type the formula below and you will get 24.67

=AVERAGEIF(B3:B17,">40",C3:C17)

COUNT Function

• If you want to count the number of cells that contain numbers in cells B3:B17, then use the formula below and you will get 15

=COUNT(B3:B17)

COUNTA Function

• If you want to count the number of nonblank cells in cells B3 through B17, then use the formula below and you will get 15

=COUNTA(B3:B17)

COUNTIF Function

• If you want to count the number of cells with 40 in cells B3 through B17, then use the formula below and you will get 1

=COUNTIF(B3: B17,40)

If you want to count the number of cells with a value greater than

40 in cells B3 through B17, then use the formula below and you will get 3

=COUNTIF(B3:B17,">40")

• If you want to count the number of cells with a value greater than

40 in cells B3 through B17 and a value greater than 20 in cells C3 through C17, then use the formula below and you will get 17

=COUNTIF(B3:B17/'>40")+COUNTIF(C3:C17,">20,,)

• If you want to count the number of cells with a value that starts with letter "B" in cells A3 through A17, then use the formula below and you will get 2

=COUNTIF(A3:A17,"B*")

MAX Function

• If you want to know the largest value in cells B3 through B17, then use the formula below and you will get 44

=MAX(B3:B17)

MIN Function

• If you want to know the smallest value in cells B3 through B17, then use the formula below and you will get 24

=MIN(B3:B17)

MEDIAN Function

• If you want to know the number in the middle of a set of numbers

in cells B3 through B17, then use the formula below and you will

get 34

=MEDIAN(B3:B17)

MODE.SNGL Function

• If you want to know the most frequently occurring, or repetitive

value in cells B3 through B17, then use the formula below and

you will get 32

=MODE.SNGL(B3:B17)

RANK.AVG Function

• If you want to know the rank of the score 32 in the cells B3nthrough B17, then use the formula below and you will get 10

=RANK.AVG(32,B3:B17)

• If you want to know the rank of your pupils based from their scores in Example, where Rank 1 is the one who got the highest, then use the formula below. You will get Rank 5.5 for Xavier.

=RANK.AVG(B3,B3:B17)

• If you want to know the rank of your pupils based from their scores in Example, where Rank 1 is the one who got the lowest, then use the formula below. You will get Rank 10.5 for Xavier.

=RANK.AVG(B3,B3:B17,1)

In RANK. A VG function, if Order is 0 or omitted, Excel ranks number

\ as if ref were a list sorted in descending order. If Order is any

nonzero value, Excel ranks number as if ref were a list sorted in

ascending order.