IF Functions

This function returns cell values as specified by a true or false condition. It is typed as `=IF` and has three components.

=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 =

Example: =IF(A2>=70,"Pass","Fail")

If the value in cell A2 is 70 or higher, Excel returns Pass; otherwise, it returns Fail.

IFS Functions

This function returns cell values as specified by multiple true or false condition. It is typed as `=IFS` and has two or more components.

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3]; ...)

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 =

Example: =IFS(A2 >= 90,"A", A2 >= 80,"B", A2 >= 70,"C", A2 < 70,"F")

This function will assign a letter grade based on the value in cell A2.

OR Functions

This function returns true or false based on two or more conditions. It is typed as `=OR` =OR([logical1], [logical2], ...). This is often used with the IF function.

The condition is referred to as logical1, 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 =

Example: =IF(OR(A2 < 60, B2="Yes"),"At Risk","OK")

Returns At Risk if the score is below 60 or attendance is marked "Yes".

AND Functions

The AND function is a function that returns TRUE or FALSE based on two or more conditions.

Example: =IF(AND(A2 >= 70, B2 >= 80),"Eligible","Not Eligible")

Returns "Eligible" only if both conditions are met.

AVERAGEIF Functions

The AVERAGEIF function calculates the average of a range based on a TRUE or FALSE condition. It is typed as `=AVERAGEIF`. The syntax for this is

=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.

Example: =AVERAGEIF(A2:A20,">= 70")

Calculates the average of all values that are 70 or higher.

Note: The [average_range] function is optional. If no range is specified, Excel will calculate the average of all the values that fit the criteria.

AVERAGEIFS Functions

The AVERAGEIFS function calculates the average of a range based on multiple TRUE or FALSE conditions. It is typed as `=AVERAGEIF`. The syntax for this is

=AVERAGEIF(average_range, criteria_range1, criteria1, … )

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. The criteria ranges are the ranges where the function check for the conditions.

Example: =AVERAGEIFS(C2:C20, A2:A20,"Running", B2:B20,"> 60")

This function calculates the average heart rate for running sessions where speed was 60 or higher.

COUNTIF Functions

This function counts cells as specified by a true or false condition. It is typed as `=COUNTIF`.

Example: =COUNTIF(A2:A30,"Absent")

This counts how many times "Absent" appears in the range.

COUNTIFS Functions

This function counts cells as specified by multiple true or false conditions. It is typed as `=COUNTIFS`.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Example: =COUNTIFS(A2:A30,"Female", B2:B30,">=80")

This counts how many female athletes scored 80 or higher.

SUMIF Functions

This function calculates the sum of values in a range based on a true or false condition.
It is typed =SUMIF: =SUMIF(range, criteria, [sum_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 [sum_range] is the range where the function calculates the sum.

Example: =SUMIF(A2:A20,"Equipment",B2:B20)

This function adds up all costs in B2:B20 where the categor in A2:A20 is "Equipment."

SUMIFS Functions

This function calculates the sum of values in a range based on a true or false condition.

It is typed =SUMIFS:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...) The condition is referred to as criteria_range1, 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 checks for the conditions.

The [sum_range] is the range where the function calculates the sum.

Example: =SUMIFS(C2:C20, A2:A20,"Running", B2:B20,">=30")

This function calculates total distance for running sessions lasting 30 minutes or more.