Excel AND Function
AND Function
The AND function is a premade function in Excel, which
returns TRUE or FALSE based on two or more conditions.
It is typed =AND and takes two or more conditions.
Note: The AND function is often used together with the IF function.
=AND([logical1], [logical2], ...)
The conditions are referred to as [logical1], [logical2], and so on.
The conditions can check things like:
If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Example AND Function
Check if the Pokemon type is fire and has speed greater than 70:
The function returns "TRUE" or "FALSE".
Example AND function, step by step:
1. Select the cell D2
2. Type =AND
3. Double click the AND command
4. Specify the first condition B2="Fire"
5. Type ,
6. Specify the second condition C2>70
7. Hit enter
Note: You can add more conditions by repeating steps 5-6 before hitting
enter.
Since the value in cell B2 is not "Fire" the first condition is FALSE.
Since the value in cell C2 is less than 70 the second condition is also FALSE.
All conditions need to be TRUE for the AND function to return TRUE.
Note: Text values needs to be in quotes: " "
The function can be repeated with the filling function for each row to perform
the same check for each Pokemon:
Now, each row has a check for Fire Type and Speed greater than 70:
Only Charmeleon and Charizard both have Fire type and speed greater than
70, so the function returns "TRUE".
ADVERTISEMENT
Example AND Function (with IF)
Combining the AND function with an IF function lets you
check multiple conditions for the IF function:
Note: The IF function lets you specify the return values.
The IF function is typed =IF and has 3 parts:
=IF(logical_test, [value_if_true], [value_if_false])
The AND function takes the place of the logical_test condition.
Here, the IF function returns "Yes" or "No".
Example AND function as the condition for IF function, step by step:
1. Select cell C2
2. Type =IF
3. Double click the IF command
4. Type AND
5. Double click the AND command
7. Specify the first condition B2="Fire"
8. Type ,
9. Specify the second condition C2>70
10. Type ),
11. Specify the value "Yes" for when both conditions are TRUE
12. Type ,
13. Specify the value "No" for when either, or both, conditions
are FALSE
14. Type ) and hit enter
The function can be repeated with the filling function for each row to perform
the same check for each Pokemon:
Now each row has a check for both being Fire type and Speed greater than
70:
Only Charmeleon and Charizard both have Fire type and speed greater than
70, so the function returns "Yes
Excel AVERAGE Function
AVERAGE Function
The AVERAGE function is a premade function in Excel, which calculates the
average (arithmetic mean).
It is typed =AVERAGE
It adds the range and divides it by the number of observations.
Example:
The average of (2, 3, 4) is 3.
3 observations (2, 3 and 4)
The sum of the observations (2 + 3 + 4 = 9)
(9 / 3 = 3)
The average is 3
Note: There are multiple types of mean values. The most common type of
mean is the arithmetic mean. You can learn more about the arithmetic mean
here: Statistics Mean Tutorial.
Note: The AVERAGE function ignores cells with text.
Let's have a look at an example where we help the Pokemon trainers to
calculate the average of Pokeballs per trainer.
How to use the =AVERAGE function, step by step:
1. Select a cell (F2)
2. Type =AVERAGE
3. Double click the AVERAGE command
4. Select a range (B2:E2)
5. Hit enter
6. Next, Fill F2:F7
Good job! You have successfully calculated the average using
the AVERAGE function.
Another Example
Example using cells with text:
The function ignores cells with text and completes the calculation.
Excel AVERAGEIF Function
AVERAGEIF Function
The AVERAGEIF function is a premade function in Excel, which calculates
the average of a range based on a true or false condition.
It is typed =AVERAGEIF and has three parts:
=AVERAGEIF(range, criteria, [average_range])
The condition is referred to as criteria, which can check things like:
If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The [average_range] is the range where the function calculates the average.
Note: The [average_range] is optional.
If not specified, the function calculates the average of the same range as
condition.
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Example AVERAGEIF function
Find the average speed of Grass type Pokemon:
The condition is that the type is "Grass".
Example AVERAGEIF function, step by step:
1. Select the cell F3
2. Type =AVERAGEIF
3. Double click the AVERAGEIF command
4. Specify the range for the condition B2:B10 (the Type 1 values)
5. Type ,
6. Specify the criteria (the cell E3, which has the value "Grass")
7. Type ,
8. Specify the range for the average C2:C10 (the Speed values)
9. Hit enter
The function now calculates the average speed value of the Grass type
Pokemon: Bulbasaur, Ivysaur and Venusaur.
The function can be repeated for Fire and Water type Pokemon to compare
them:
Note: You can use the filling function for the other rows, but make sure to
use absolute references for the ranges.
Now, we can see the average speed values of each type:
Excel AVERAGEIFS Function
AVERAGEIFS Function
The AVERAGEIFS function is a premade function in Excel, which calculates
the average of a range based on one or more true or false condition.
It is typed =AVERAGEIFS:
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
The conditions are referred to as critera1, criteria2, .. and so on, which
can check things like:
If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The criteria_range1, criteria_range2, and so on, are the ranges where the
function check for the conditions.
The average_range is the range where the function calculates the average.
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Example AVERAGEIFS function
Find the average defense of Grass type 1st Generation Pokemon:
The conditions are that the type is "Grass" and Generation is 1.
Example AVERAGEIFS function, step by step:
1. Select the cell H3
2. Type =AVERAGEIFS
3. Double click the AVERAGEIFS command
4. Specify the range for the average C2:C13 (the Defense values)
5. Type ,
6. Specify the range for the first conditionB2:B13 (the Type 1 values)
7. Type ,
8. Specify the criteria (the cell F3, which has the value "Grass")
9. Type ,
10. Specify the range for the second conditionD2:D13 (the Generation
values)
11. Type ,
12. Specify the criteria (the cell G3, which has the value "1")
13. Hit enter
Note: You can add more conditions by repeating steps 9-12 before hitting
enter.
The function now calculates the average defense value of the 1st Generation
Grass type Pokemon: Bulbasaur, Ivysaur and Venusaur.
The function can be repeated for Fire type Pokemon and 2nd Generation to
compare them:
Note: You can use the filling function for the other rows, but make sure to
use absolute references for the ranges.
Now, we can see the average defense values of each type between
generations:
Notice how the 2nd Generation Grass type Pokemon got more defense, but
the Fire type stayed the same.
Excel CONCAT Function
CONCAT Function
To concatenate is to link something together.
CONCAT is a function in Excel and is short for concatenate.
The CONCAT function is used to link multiple cells without adding any
delimiters between the combined cell values.
It is typed =CONCAT
=CONCAT(cell1, delimiter, cell2)
Note: Delimiters are spacing or symbols used to seperate content elements
apart from each other.
Example of delimiters
comma , semicolon ; quotes " or ' braces {} pipes | slashes / \
Info: CONCAT is a new version of the old CONCATENATE function. Recent
versions of Excel do not accept the old CONCATENATE function as it is only
compatible with earlier versions of Excel. To use COCATENATE in the latest
versions of Excel, use CONCAT.
How to Use CONCAT Function
To combine values from multiple Excel cells, use CONCAT.
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Step 1) Start the CONCAT function
1. Select a cell E2
2. Type =CONCAT
3. Double click the CONCAT command
Follow along the tutorial by trying it yourself!
Copy the values in the example above and try it on your own!
Step 2) Link cells
4. Select a cell (A2)
5. Add a comma (,)
6. Add a space inside of quotation marks to create a delimiter ( " ")
7. Select another cell (A3)
8. Hit enter
Note: The CONCAT function does not provide any delimiters between cell
values used in the funtion. If you want your cell values to be displayed with
delimiters, you have to add the delimiters between the selected cells. You
can use any symbol as a demiliter, if you want the delimiter to be a space,
enter " ".
The function returns the content of A2 and A3 separated with a " " dilimiter.
You can also use more symbols as a demilter, such as adding the word " and
".
The CONCAT function accepts cell values of both text and numbers, you can
combine them in any way you prefer.
Combine the cell values with text strings as delimiters to make the output
more descriptive!
Excel COUNT Function
COUNT Function
The COUNT function is a premade function in Excel, which counts cells with
numbers in a range.
It is typed =COUNT
Note: The COUNT function only counts cells with numbers, not cells with
letters. The COUNTA function is better used if the cells have letters.
How to use the =COUNT function:
1. Select a cell
2. Type =COUNT
3. Double click the COUNT command
4. Select a range
5. Hit enter
Let's see some examples!
Apply the =COUNT function to range D2:D21. Counting the cells of Pokemon
Total stats, which is numbers only:
COUNT function, step by step:
1. Select D23
2. Type =COUNT
3. Double click COUNT in the menu
4. Select range D2:D21
5. Hit enter
Note: The =COUNT function only counts cells with numbers in a range.
That's it! The =COUNT function successfully counted 20 cells with numbers.
ADVERTISEMENT
A Non-Working Example
Now, let us try an example that will not work.
Apply the =COUNT function to the range A2:A21, which only has cells with
letters.
It returns the value 0, no cells with numbers found. Range A2:A21 only had
cells with letters.
Excel COUNTA Function
COUNTA Function
The COUNTA function is a premade function in Excel, which counts all cells in
a range that has values, both numbers and letters.
It is typed =COUNTA
How to use the =COUNTA function:
1. Select a cell
2. Type =COUNTA
3. Double click the COUNTA command
4. Select a range
5. Hit enter
Let's see some examples!
Apply the =COUNTA function to range A2:A21, counting Pokemons by their
names, which are letters only:
COUNTA function, step by step:
1. Select A23
2. Type =COUNTA
3. Double click COUNTA in the menu
4. Select range A2:A21
5. Hit enter
The COUNTA function has successfully counted 20 cells with values in the
range A2:A21.
Lets apply the COUNTA function to D2:D21. Counting a range with numbers
only.
That is great! The COUNTA function counts cells in a range with values both
numbers and letters.
Excel COUNTBLANK
Function
COUNTBLANK Function
The COUNTBLANK function is a premade function in Excel, which counts
blank cells in a range.
It is typed =COUNTBLANK
Note: The COUNTBLANK function is helpful to find empty cells in a range.
How to use the =COUNTBLANK function:
1. Select a cell
2. Type =COUNTBLANK
3. Double click the COUNTBLANK command
4. Select a range
5. Hit enter
Let's see some examples!
Apply the =COUNTBLANK function to range C2:C21, to find the Pokemons
which do not have a 2nd Type:
COUNTBLANK function, step by step:
1. Select C23
2. Write =COUNTBLANK
3. Double click COUNTBLANK in the menu
4. Select range C2:C21
5. Hit enter
The COUNTBLANK function successfully counted 8 blank cells in the
range C2:C21.
Excel COUNTIF Function
COUNTIF Function
The COUNTIF function is a premade function in Excel, which counts cells as
specified.
It is typed =COUNTIF
NOTE: The COUNTIF function can have basic or more advanced uses. This
covers the basic use for how to count specific numbers and words.
Numbers (e.g. 90) and words (e.g. "Water") can be specified.
How to use the =COUNTIF function:
1. Select a cell
2. Type =COUNTIF
3. Double click the COUNTIF command
4. Select a range
5. Type ,
6. Select a cell (the criteria, the value that you want to count)
7. Hit enter
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Let's see some examples!
Apply the COUNTIF function to range B2:B21, to count how many Pokemons
we have in the different types:
We want the COUNTIF function to count the types of Pokemons, in the
range G5:G15:
The , is typed after the range is selected, which tells the function what you
are looking to count.
The COUNTIF function has successfully counted 1 Grass Pokemon, which is
Victreebel (A4).
Let's count the Water Pokemons in G6
The same steps apply
1. Select G6
2. Type =COUNTIF
3. Select B2:B21
4. Type (,)
5. Select F6 (Specifying Water as criteria)
6. Hit enter
That is great! The COUNTIF function has counted 6 Water Pokemons
in B2:B21.
Let's count the rest of the types more effectively. We want to continue the
function from G6:G15. Making use of the Filling Function and Absolute
References.
Step by step:
1. Double click G6
2. Lock the range references absolute (B2:B21). Type dollar signs before
the columns and row. Type 4 dollar signs in
total. =COUNTIF($B$2:$B$21,F6). Note: We want F6 to remain
relative. Because we want it to move downwards. Do not add dollar
signs ($) to it.
3. Hit enter
4. Fill the range G6:G15
That saved us alot of time! Good job!
ADVERTISEMENT
A Non-Working Example
Let's try an example that will not work
Fill G5:G15 without locking the references to see what happens.
If the references for the range are kept relative, the fill function will move the
range downwards, including blank cells and missing the range where the data
is.
Excel COUNTIFS Function
COUNTIFS Function
The COUNTIFS function is a premade function in Excel, which counts cells in
a range based on one or more true or false condition.
It is typed =COUNTIFS:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2,
criteria2], ...)
The conditions are referred to as critera1, criteria2, .. and so on,
which can check things like:
If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
The criteria_range1, criteria_range2, and so on, are the ranges where
the function check for the conditions.
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Example COUNTIFS function
Find the number of 1st Generation Water type Pokemon:
The conditions are that the type is "Water" and Generation is 1.
Note: The full dataset continues after row 14, all the way down to row 759.
Example COUNTIFS function, step by step:
1. Select the cell G3
2. Type =COUNTIFS
3. Double click the COUNTIFS command
4. Specify the range for the type B2:B759 (the Type 1 values)
5. Type ,
6. Specify the criteria (the cell E3, which has the value "Water")
7. Type ,
8. Specify the range for the second conditionC2:C759 (the Generation
values)
9. Type ,
10. Specify the criteria (the cell F3, which has the value "1")
11. Hit enter
Note: You can add more conditions by repeating steps 7-10 before hitting
enter.
The function now counts the number of 1st Generation Water type Pokemon.
The function can be repeated for Water type Pokemon for the following
Generations to compare them:
Note: You can use the filling function for the other rows, but make sure to
use absolute references for the ranges.
Now, we can see the total number of Water type Pokemon between
generations 1-4:
Excel IF Function
IF Function
The IF function is a premade function in Excel, which returns values based on
a true or false condition.
It is typed =IF and has 3 parts:
=IF(logical_test, [value_if_true], [value_if_false])
The condition is referred to as logical_test, which can check things like:
If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
Note: You can decide both the return values and the condition.
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Example IF function (equal to)
Check if the Pokemon type is grass:
The condition is if the "Type 1" value for the Pokemon is "Grass".
The function returns "Yes" or "No".
Example IF function, step by step:
1. Select the cell D2
2. Type =IF
3. Double click the IF command
4. Specify the condition B2="Grass"
5. Type ,
6. Specify the value "Yes" for when the condition is TRUE
7. Type ,
8. Specify the value "No" for when the condition is FALSE
9. Hit enter
Since the value in cell B2 is "Grass", the condition is true and the function
will return "Yes".
Note: Text values needs to be in quotes: " "
The function can be repeated with the filling function for each row to perform
the same check for each Pokemon:
Now, each row has a check for Grass Type:
ADVERTISEMENT
Example IF Function (greater than)
Check if the Pokemon's total stats is greater than 500:
The function returns "Yes" or "No".
Example IF function, step by step:
1. Select the cell D2
2. Type =IF
3. Double click the IF command
4. Specify the condition C2>500
5. Type ,
6. Specify the value "Yes" for when the condition is TRUE
7. Type ,
8. Specify the value "No" for when the condition is FALSE
9. Hit enter
Since the value in cell C2 is 318, the condition is false and the function will
return "No".
The function can be repeated with the filling function for each row to perform
the same check for each Pokemon:
Now, each row has a check for having more than 500 total stats:
Excel IFS Function
IFS Function
The IFS function is a premade function in Excel, which returns values based
on one or more true or false conditions.
It is typed =IFS and has two or more parts:
=IFS(logical_test1, value_if_true1, [logical_test2,
value_if_true2], [logical_test3; ...)
The conditions are referred to as logical_test1, logical_test2, ...,
which can check things like:
If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
Each condition is connected with a return value.
Note: More than one condition can be true so the function will return the
value for the first true condition.
Note: The different parts of the function are separated by a symbol, like
comma , or semicolon ;
The symbol depends on your Language Settings.
Example IFS function
Make categories for how fast the Pokemon are:
The conditions and return values are:
Speed more than 90: "Fast"
Speed more than 50: "Normal"
Speed less than or equal to 50: "Slow"
Example IFS function, step by step:
1. Select the cell D2
2. Type =IFS
3. Double click the IFS command
4. Specify the first condition C2>90
5. Type ,
6. Specify the value "Fast" for when the first condition is TRUE
7. Type ,
8. Specify the second condition C2>50
9. Type ,
10. Specify the value "Normal" for when the second condition
is TRUE
11. Type ,
12. Specify the third condition C2<=50
13. Type ,
14. Specify the value "Slow" for when the third condition is TRUE
15. Hit enter
Since the value in cell C2 is "45", the first and second conditions are false,
and third condition is true (less than or equal to 50), the function will return
"Slow".
Note: Text values needs to be in quotes: " "
The function can be repeated with the filling function for each row to perform
the same check for each Pokemon:
Note: The third condition <=50 includes = so that 50 is included in "less than
or equal to 50"
Now, each Pokemon has a speed category:
