खाटू श्याम जी की, कृपा से
Introduction
This chapter explores some essential logical functions in Excel, including XOR, SUMIF, AVERAGEIF, COUNTIF, SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, IFERROR, and IFNA. These functions are crucial for performing conditional calculations and data analysis.
1. XOR Function
- Description: The XOR function (exclusive OR) returns TRUE if only one of the conditions is TRUE. If both conditions are TRUE or both are FALSE, it returns FALSE.
- Syntax: XOR(logical1, [logical2], …)
- Example 1: =XOR(A1 > 10, B1 < 5)
Returns TRUE if either A1 is greater than 10 or B1 is less than 5, but not both. - Example 2: =XOR(ISNUMBER(A1), ISNUMBER(B1))
Returns TRUE if exactly one of A1 or B1 is a number.
2. SUMIF Function
- Description: Adds the cells specified by a given condition or criteria.
- Syntax: SUMIF(range, criteria, [sum_range])
- Example 1: =SUMIF(A1:A10, “>10”, B1:B10)
Sums values in B1:B10 where corresponding values in A1:A10 are greater than 10. - Example 2: =SUMIF(C1:C10, “Apple”, D1:D10)
Adds values in D1:D10 where C1:C10 contains “Apple”.
3. AVERAGEIF Function
- Description: Calculates the average of cells specified by a given condition or criteria.
- Syntax: AVERAGEIF(range, criteria, [average_range])
- Example 1: =AVERAGEIF(A1:A10, “<100”, B1:B10)
Averages values in B1:B10 where corresponding values in A1:A10 are less than 100. - Example 2: =AVERAGEIF(E1:E10, “>=50”, F1:F10)
Averages values in F1:F10 where E1:E10 is greater than or equal to 50.
4. COUNTIF Function
- Description: Counts the number of cells that meet a criterion.
- Syntax: COUNTIF(range, criteria)
- Example 1: =COUNTIF(A1:A10, “Yes”)
Counts cells in A1:A10 that contain “Yes”. - Example 2: =COUNTIF(B1:B10, “<=50”)
Counts cells in B1:B10 that are less than or equal to 50.
5. SUMIFS Function
- Description: Adds the cells that meet multiple criteria.
- Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Example 1: =SUMIFS(B1:B10, A1:A10, “>10”, C1:C10, “Apple”)
Sums values in B1:B10 where corresponding values in A1:A10 are greater than 10 and C1:C10 contains “Apple”. - Example 2: =SUMIFS(D1:D10, E1:E10, “<100”, F1:F10, “>=50”)
Adds values in D1:D10 where E1:E10 is less than 100 and F1:F10 is greater than or equal to 50.
6. COUNTIFS Function
- Description: Counts the number of cells that meet multiple criteria.
- Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Example 1: =COUNTIFS(A1:A10, “>10”, B1:B10, “<5”)
Counts cells in A1:A10 where the value is greater than 10 and in B1:B10 where the value is less than 5. - Example 2: =COUNTIFS(C1:C10, “Yes”, D1:D10, “>=50”)
Counts cells in C1:C10 that contain “Yes” and D1:D10 that are greater than or equal to 50.
7. AVERAGEIFS Function
- Description: Averages cells that meet multiple criteria.
- Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Example 1: =AVERAGEIFS(B1:B10, A1:A10, “>10”, C1:C10, “Apple”)
Averages values in B1:B10 where corresponding values in A1:A10 are greater than 10 and C1:C10 contains “Apple”. - Example 2: =AVERAGEIFS(F1:F10, E1:E10, “<100”, G1:G10, “>=50”)
Averages values in F1:F10 where E1:E10 is less than 100 and G1:G10 is greater than or equal to 50.
8. MAXIFS Function
- Description: Returns the maximum value among cells that meet multiple criteria.
- Syntax: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Example 1: =MAXIFS(B1:B10, A1:A10, “>10”, C1:C10, “Apple”)
Finds the maximum value in B1:B10 where A1:A10 is greater than 10 and C1:C10 contains “Apple”. - Example 2: =MAXIFS(D1:D10, E1:E10, “<100”, F1:F10, “>=50”)
Finds the maximum value in D1:D10 where E1:E10 is less than 100 and F1:F10 is greater than or equal to 50.
9. MINIFS Function
- Description: Returns the minimum value among cells that meet multiple criteria.
- Syntax: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Example 1: =MINIFS(B1:B10, A1:A10, “>10”, C1:C10, “Apple”)
Finds the minimum value in B1:B10 where A1:A10 is greater than 10 and C1:C10 contains “Apple”. - Example 2: =MINIFS(D1:D10, E1:E10, “<100”, F1:F10, “>=50”)
Finds the minimum value in D1:D10 where E1:E10 is less than 100 and F1:F10 is greater than or equal to 50.
10. IFERROR Function
- Description: Returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.
- Syntax: IFERROR(value, value_if_error)
- Example 1: =IFERROR(A1/B1, “Error in calculation”)
Returns “Error in calculation” if the division results in an error (e.g., dividing by zero). - Example 2: =IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), “Not Found”)
Returns “Not Found” if VLOOKUP does not find a match.
11. IFNA Function
- Description: Similar to IFERROR, but specifically for #N/A errors.
- Syntax: IFNA(value, value_if_na)
- Example 1: =IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), “Not Found”)
Returns “Not Found” if VLOOKUP results in a #N/A error. - Example 2: =IFNA(MATCH(A1, B1:B10, 0), “Not Found”)
Returns “Not Found” if MATCH does not find a match.
Practice Assignment
- Using IFERROR and IFNA:
- Create a formula using IFERROR to handle division by zero errors. Then use IFNA to handle #N/A errors in a VLOOKUP function.
- SUMIF, AVERAGEIF, and COUNTIF:
- Create a summary table where you use SUMIF to calculate total sales, AVERAGEIF to find the average sales, and COUNTIF to count the number of sales above a certain amount.
- SUMIFS, COUNTIFS, and AVERAGEIFS:
- Create a detailed report using SUMIFS to sum sales data based
