UNIT - I
In Excel, to calculate the "SUM" of a range of cells, use the formula =SUM(range), to calculate
the "AVERAGE" use =AVERAGE(range), and to count the number of cells within a range,
use =COUNT(range).
Explanation:
SUM(range): Adds up all the numerical values within the specified range of cells.
AVERAGE(range): Calculates the arithmetic mean (average) of the values within the
specified range.
COUNT(range): Counts the number of cells within the specified range that contain
numerical values.
Example:
To calculate the sum of cells A1 to A5, use the formula =SUM(A1:A5).
To calculate the average of cells B2 to B10, use the formula =AVERAGE(B2:B10).
To count the number of values in cells C1 to C10, use the formula =COUNT(C1:C10)
What is a Logical Function?
It is a feature that allows us to introduce decision-making when executing formulas and
functions. Functions are used to;
Check if a condition is true or false
Combine multiple conditions together
What is a condition and why does it matter?
A condition is an expression that either evaluates to true or false. The expression could be a
function that determines if the value entered in a cell is of numeric or text data type, if a value is
greater than, equal to or less than a specified value, etc.
IF Function example
We will work with the home supplies budget from this tutorial. We will use the IF function to
determine if an item is expensive or not. We will assume that items with a value greater than
6,000 are expensive. Those that are less than 6,000 are less expensive. The following image
shows us the dataset that we will work with.
, Put the cursor focus in cell F4
Enter the following formula that uses the IF function
=IF(E4<6000,”Yes”,”No”)
HERE,
“=IF(…)” calls the IF functions
“E4<6000” is the condition that the IF function evaluates. It checks the value of cell
address E4 (subtotal) is less than 6,000
“Yes” this is the value that the function will display if the value of E4 is less than 6,000
“No” this is the value that the function will display if the value of E4 is greater than 6,000
When you are done press the enter key
You will get the following results
Excel Logic functions explained
The following table shows all of the logical functions in Excel
, S/ FUNCT CATEGOR
DESCRIPTION USAGE
N ION Y
=AND(1 > 0,ISNUMBER(1))
Checks multiple conditions and returns true if The above function returns
01 AND Logical
they all the conditions evaluate to true. TRUE because both Condition is
True.
Returns the logical value FALSE. It is used to
02 FALSE Logical compare the results of a condition or function FALSE()
that either returns true or false
Verifies whether a condition is met or not. If the =IF(ISNUMBER(22),”Yes”,
condition is met, it returns true. If the condition “No”)
03 IF Logical
is not met, it returns false. 22 is Number so that it return
=IF(logical_test,[value_if_true],[value_if_false]) Yes.
IFERR Returns the expression value if no error occurs. =IFERROR(5/0,”Divide by zero
04 Logical
OR If an error occurs, it returns the error value error”)
Returns value if #N/A error does not occur. If =IFNA(D6*E6,0)
#N/A error occurs, it returns NA value. #N/A N.B the above formula returns
05 IFNA Logical
error means a value if not available to a formula zero if both or either D6 or E6
or function. is/are empty
=NOT(ISTEXT(0))
N.B. the above function returns
Returns true if the condition is false and returns
06 NOT Logical true. This is because ISTEXT(0)
false if condition is true
returns false and NOT function
converts false to TRUE
Used when evaluating multiple conditions. =OR(D8=”admin”,E8=”cashier”
Returns true if any or all of the conditions are N.B. the above function returns
07 OR Logical
true. Returns false if all of the conditions are true if either or both D8 and E8
false admin or cashier
To concatenate the "left," "right," and "mid" parts of a string in Excel, you would use the
formula =CONCATENATE(LEFT(text, num_left), MID(text, start_num, num_mid),
RIGHT(text, num_right)), where "text" is the original string, "num_left" is the number of
characters to extract from the left, "start_num" is the starting position for the middle section, and
In Excel, to calculate the "SUM" of a range of cells, use the formula =SUM(range), to calculate
the "AVERAGE" use =AVERAGE(range), and to count the number of cells within a range,
use =COUNT(range).
Explanation:
SUM(range): Adds up all the numerical values within the specified range of cells.
AVERAGE(range): Calculates the arithmetic mean (average) of the values within the
specified range.
COUNT(range): Counts the number of cells within the specified range that contain
numerical values.
Example:
To calculate the sum of cells A1 to A5, use the formula =SUM(A1:A5).
To calculate the average of cells B2 to B10, use the formula =AVERAGE(B2:B10).
To count the number of values in cells C1 to C10, use the formula =COUNT(C1:C10)
What is a Logical Function?
It is a feature that allows us to introduce decision-making when executing formulas and
functions. Functions are used to;
Check if a condition is true or false
Combine multiple conditions together
What is a condition and why does it matter?
A condition is an expression that either evaluates to true or false. The expression could be a
function that determines if the value entered in a cell is of numeric or text data type, if a value is
greater than, equal to or less than a specified value, etc.
IF Function example
We will work with the home supplies budget from this tutorial. We will use the IF function to
determine if an item is expensive or not. We will assume that items with a value greater than
6,000 are expensive. Those that are less than 6,000 are less expensive. The following image
shows us the dataset that we will work with.
, Put the cursor focus in cell F4
Enter the following formula that uses the IF function
=IF(E4<6000,”Yes”,”No”)
HERE,
“=IF(…)” calls the IF functions
“E4<6000” is the condition that the IF function evaluates. It checks the value of cell
address E4 (subtotal) is less than 6,000
“Yes” this is the value that the function will display if the value of E4 is less than 6,000
“No” this is the value that the function will display if the value of E4 is greater than 6,000
When you are done press the enter key
You will get the following results
Excel Logic functions explained
The following table shows all of the logical functions in Excel
, S/ FUNCT CATEGOR
DESCRIPTION USAGE
N ION Y
=AND(1 > 0,ISNUMBER(1))
Checks multiple conditions and returns true if The above function returns
01 AND Logical
they all the conditions evaluate to true. TRUE because both Condition is
True.
Returns the logical value FALSE. It is used to
02 FALSE Logical compare the results of a condition or function FALSE()
that either returns true or false
Verifies whether a condition is met or not. If the =IF(ISNUMBER(22),”Yes”,
condition is met, it returns true. If the condition “No”)
03 IF Logical
is not met, it returns false. 22 is Number so that it return
=IF(logical_test,[value_if_true],[value_if_false]) Yes.
IFERR Returns the expression value if no error occurs. =IFERROR(5/0,”Divide by zero
04 Logical
OR If an error occurs, it returns the error value error”)
Returns value if #N/A error does not occur. If =IFNA(D6*E6,0)
#N/A error occurs, it returns NA value. #N/A N.B the above formula returns
05 IFNA Logical
error means a value if not available to a formula zero if both or either D6 or E6
or function. is/are empty
=NOT(ISTEXT(0))
N.B. the above function returns
Returns true if the condition is false and returns
06 NOT Logical true. This is because ISTEXT(0)
false if condition is true
returns false and NOT function
converts false to TRUE
Used when evaluating multiple conditions. =OR(D8=”admin”,E8=”cashier”
Returns true if any or all of the conditions are N.B. the above function returns
07 OR Logical
true. Returns false if all of the conditions are true if either or both D8 and E8
false admin or cashier
To concatenate the "left," "right," and "mid" parts of a string in Excel, you would use the
formula =CONCATENATE(LEFT(text, num_left), MID(text, start_num, num_mid),
RIGHT(text, num_right)), where "text" is the original string, "num_left" is the number of
characters to extract from the left, "start_num" is the starting position for the middle section, and
