Logical Formulas in excel

-

 


Logical formulas in Excel are used to perform operations based on logical conditions. These formulas evaluate whether a given condition is true or false and then perform a specified action accordingly. Here are some commonly used logical formulas in Excel:

  1. IF Function:

    • Syntax: =IF(logical_test, value_if_true, value_if_false)
    • Example: =IF(A1>10, "Yes", "No")
    • This formula checks if the value in cell A1 is greater than 10. If true, it returns "Yes"; otherwise, it returns "No".

  2. AND Function:

    • Syntax: =AND(logical1, logical2, ...)
    • Example: =AND(A1>10, B1<20)
    • Returns TRUE if both conditions (A1 > 10 and B1 < 20) are true; otherwise, it returns FALSE.

  3. OR Function:

    • Syntax: =OR(logical1, logical2, ...)
    • Example: =OR(A1="Apple", A1="Banana")
    • Returns TRUE if at least one of the conditions (A1 is "Apple" or A1 is "Banana") is true; otherwise, it returns FALSE.

  4. NOT Function:

    • Syntax: =NOT(logical)
    • Example: =NOT(A1="Closed")
    • Returns TRUE if the condition (A1 is not "Closed") is false; otherwise, it returns FALSE.

  5. IFERROR Function:

    • Syntax: =IFERROR(value, value_if_error)
    • Example: =IFERROR(A1/B1, "Error in calculation")
    • Returns the result of the division in A1 by B1 unless there is an error, in which case it returns "Error in calculation".

      1. IF AND OR Combination:

        • Syntax: =IF(AND(logical1, logical2), value_if_true, value_if_false)
        • Example: =IF(AND(A1>10, B1<20), "Yes", "No")
        • Returns "Yes" if both conditions (A1 > 10 and B1 < 20) are true; otherwise, it returns "No".

      2. IF with Nested IF Statements:

        • Syntax: =IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, value_if_false2))
        • Example: =IF(A1>10, "High", IF(A1>5, "Medium", "Low"))
        • Nested IF statement that categorizes values in cell A1 as "High," "Medium," or "Low" based on different conditions.

      3. XOR Function (Exclusive OR):

        • Syntax: =XOR(logical1, logical2)
        • Example: =XOR(A1>10, B1<20)
        • Returns TRUE if either logical1 is true or logical2 is true, but not both.

      4. COUNTIF Function:

        • Syntax: =COUNTIF(range, criteria)
        • Example: =COUNTIF(A1:A10, ">50")
        • Counts the number of cells in the range A1:A10 that are greater than 50.

      5. SUMIF Function:

        • Syntax: =SUMIF(range, criteria, sum_range)
        • Example: =SUMIF(A1:A10, ">50", B1:B10)
        • Adds up the values in the range B1:B10 corresponding to cells in A1:A10 that meet the criteria (>50).

      6. IF Statement with Multiple Conditions (Using AND and OR):

        • Syntax: =IF(AND(condition1, OR(condition2, condition3)), value_if_true, value_if_false)
        • Example: =IF(AND(A1>10, OR(B1="High", B1="Medium")), "Yes", "No")
        • Returns "Yes" if A1 is greater than 10 and B1 is either "High" or "Medium"; otherwise, it returns "No".

      7. IFS Function (Multiple IF Conditions):

        • Syntax: =IFS(condition1, value_if_true1, condition2, value_if_true2, ..., value_if_false)
        • Example: =IFS(A1>10, "High", A1>5, "Medium", A1<=5, "Low")
        • Evaluates multiple conditions and returns the corresponding value for the first true condition.

      8. CHOOSE Function (Selecting Values Based on Index):

        • Syntax: =CHOOSE(index, value1, value2, ...)
        • Example: =CHOOSE(A1, "Option 1", "Option 2", "Option 3")
        • Returns the value at the position specified by the value in cell A1.

      9. IFNA Function (Handling #N/A Errors):

        • Syntax: =IFNA(value, value_if_na)
        • Example: =IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")
        • Returns the result of the VLOOKUP function unless it results in #N/A error, in which case it returns "Not Found".

      10. IFERROR with VLOOKUP (Handling Errors):

        • Syntax: =IFERROR(formula, value_if_error)
        • Example: =IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), "Error")
        • Returns the result of the VLOOKUP function unless there is an error, in which case it returns "Error".

      11. IFS Function:

        • Syntax: =IFS(condition1, value_if_true1, condition2, value_if_true2, ...)
        • Example: =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "Fail")
        • Returns a grade based on the value in cell A1. If A1 is greater than 90, it returns "A," if greater than 80, it returns "B," and so on.

      12. IFERROR with VLOOKUP:

        • Syntax: =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), "Not Found")
        • Example: =IFERROR(VLOOKUP(A1, $B$1:$D$100, 2, FALSE), "Not Found")
        • Uses VLOOKUP to search for a value in column B and returns the corresponding value from column C. If an error occurs, it returns "Not Found."

      13. MATCH Function:

        • Syntax: =MATCH(lookup_value, lookup_array, [match_type])
        • Example: =MATCH("Apple", A1:A10, 0)
        • Returns the relative position of "Apple" within the range A1:A10. The match_type 0 ensures an exact match.

      14. IF with ISBLANK Function:

        • Syntax: =IF(ISBLANK(A1), "Blank", "Not Blank")
        • Example: Checks if cell A1 is blank. If true, it returns "Blank"; otherwise, it returns "Not Blank."

      15. LEN Function for Text Length:

        • Syntax: =LEN(A1)
        • Example: =LEN("Excel")
        • Returns the number of characters in cell A1 or the specified text. In this example, it returns 5.

      16. COUNTIFS Function:

        • Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)
        • Example: =COUNTIFS(A1:A10, ">50", B1:B10, "<100")
        • Counts the number of cells in A1:A10 where the value is greater than 50 and simultaneously in B1:B10 where the value is less than 100.

      17. SUMIFS Function:

        • Syntax: =SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)
        • Example: =SUMIFS(C1:C10, A1:A10, "Apples", B1:B10, "January")
        • Adds up the values in C1:C10 where the corresponding cells in A1:A10 contain "Apples" and B1:B10 contain "January."

      18. TEXT Function with IF:

        • Syntax: =IF(A1>0, TEXT(A1, "0.00"), "Negative")
        • Example: Formats the positive values in cell A1 with two decimal places using the TEXT function. If A1 is negative, it returns "Negative."

      19. Logical Formulas with Dates:

        • Syntax: =IF(TODAY()>A1, "Past", "Future")
        • Example: Compares the date in cell A1 with the current date. If A1 is in the past, it returns "Past"; otherwise, it returns "Future."

      20. IF with ISNUMBER and SEARCH:

        • Syntax: =IF(ISNUMBER(SEARCH("apple", A1)), "Contains Apple", "No Apple")
        • Example: Checks if cell A1 contains the word "apple" (case-insensitive). If true, it returns "Contains Apple"; otherwise, it returns "No Apple."

      21. CHOOSE Function:

        • Syntax: =CHOOSE(index_num, value1, value2, ...)
        • Example: =CHOOSE(A1, "Option 1", "Option 2", "Option 3")
        • Returns the value based on the index number in cell A1. For example, if A1 is 2, it returns "Option 2."

      22. IF with ISTEXT:

        • Syntax: =IF(ISTEXT(A1), "Text", "Not Text")
        • Example: Checks if the content in cell A1 is text. If true, it returns "Text"; otherwise, it returns "Not Text."

      23. IFS with Wildcards:

        • Syntax: =IFS(ISNUMBER(SEARCH("apple", A1)), "Fruit", ISNUMBER(SEARCH("car", A1)), "Vehicle", TRUE, "Other")
        • Example: Uses IFS to categorize the content in cell A1 based on whether it contains "apple" (Fruit), "car" (Vehicle), or neither (Other).

      24. IF with TODAY and EDATE:

        • Syntax: =IF(TODAY()>EDATE(A1, 3), "Expired", "Active")
        • Example: Checks if the date in cell A1 is more than 3 months ago. If true, it returns "Expired"; otherwise, it returns "Active."

      25. INDEX and MATCH Combination:

        • Syntax: =INDEX(B1:B10, MATCH(MAX(A1:A10), A1:A10, 0))
        • Example: Finds the maximum value in A1:A10 and returns the corresponding value from B1:B10 using INDEX and MATCH.

      26. IF with OR and AND:
        • Syntax: =IF(OR(A1>10, AND(B1<5, C1="Yes")), "Condition Met", "Condition Not Met")
        • Example: Checks if either A1 is greater than 10 or both B1 is less than 5 and C1 is "Yes." If true, it returns "Condition Met"; otherwise, it returns "Condition Not Met."
        1. INDEX and MATCH for Dynamic Lookups:
        • Syntax: =INDEX($C$1:$C$100, MATCH(MAX($B$1:$B$100), $B$1:$B$100, 0))
        • Example: Retrieves the value from column C corresponding to the maximum value in column B, creating a dynamic lookup.
        1. IF with DATE Function:
        • Syntax: =IF(DATE(YEAR(A1), MONTH(A1)+3, DAY(A1))<TODAY(), "Older Than 3 Months", "Within 3 Months")
        • Example: Compares the date in cell A1 with a date that is 3 months later. If the date is older, it returns "Older Than 3 Months"; otherwise, it returns "Within 3 Months."
        1. Nested IF Statements for Grading:
        • Syntax: =IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", "Fail")))
        • Example: Uses nested IF statements to assign grades based on the value in cell A1.
        1. IF with ISEMPTY:
        • Syntax: =IF(ISEMPTY(A1), "Empty", "Not Empty")
        • Example: Checks if cell A1 is empty. If true, it returns "Empty"; otherwise, it returns "Not Empty."
      1. IF with MOD Function:
      • Syntax: =IF(MOD(A1, 2) = 0, "Even", "Odd")
      • Example: Determines if the value in cell A1 is even or odd using the MOD function within an IF statement.
      1. COUNTIF with Multiple Criteria:
      • Syntax: =COUNTIFS(A1:A10, ">50", B1:B10, "Red")
      • Example: Counts the number of cells where the value in column A is greater than 50 and the value in column B is "Red."
      1. SUMPRODUCT for Weighted Average:
      • Syntax: =SUMPRODUCT(A1:A5, B1:B5) / SUM(B1:B5)
      • Example: Calculates the weighted average of values in column A based on weights in column B using SUMPRODUCT.
      1. IF with ISTIME:
      • Syntax: =IF(ISTIME(A1), "Time", "Not Time")
      • Example: Checks if the value in cell A1 is a valid time. If true, it returns "Time"; otherwise, it returns "Not Time."
      1. INDEX and MATCH with Multiple Criteria:
      • Syntax: =INDEX(D1:D10, MATCH(1, (A1:A10="Apples")*(B1:B10="Red"), 0))
      • Example: Retrieves a value from column D where column A is "Apples" and column B is "Red" using INDEX and MATCH with multiple criteria.
    • IF with ISNUMBER and FIND for Substring Search:
      • Syntax: =IF(ISNUMBER(FIND("excel", A1)), "Contains 'excel'", "Doesn't Contain 'excel'")
      • Example: Checks if the text in cell A1 contains the substring "excel." If true, it returns "Contains 'excel'"; otherwise, it returns "Doesn't Contain 'excel'."
      1. SUMPRODUCT for Multiple Conditions:
      • Syntax: =SUMPRODUCT((A1:A10="Apples")*(B1:B10="Red")*(C1:C10>5), D1:D10)
      • Example: Calculates the sum of values in column D where conditions are met in columns A, B, and C using SUMPRODUCT.
      1. IF with WEEKDAY Function:
      • Syntax: =IF(WEEKDAY(A1)=1, "Sunday", "Not Sunday")
      • Example: Determines if the date in cell A1 falls on a Sunday using the WEEKDAY function within an IF statement.
      1. INDEX and MATCH with Approximate Match:
      • Syntax: =INDEX(C1:C10, MATCH(MAX(A1:A10), A1:A10, 1))
      • Example: Retrieves the value from column C corresponding to the maximum value in column A using INDEX and MATCH with an approximate match.
      1. IF with NETWORKDAYS Function:
      • Syntax: =IF(NETWORKDAYS(A1, B1)>5, "More than 5 workdays", "5 or fewer workdays")
      • Example: Checks if the number of workdays between dates in cells A1 and B1 is more than 5 using the NETWORKDAYS function within an IF statement.
      1. IF with ISTEXT and LEN for Text Length Validation:
      • Syntax: =IF(ISTEXT(A1), IF(LEN(A1)<=10, "Valid", "Too Long"), "Not Text")
      • Example: Checks if cell A1 contains text. If true, it further checks if the length is less than or equal to 10 characters, returning "Valid" or "Too Long" accordingly.
      1. IFS with ISTEXT and ISNUMBER:
      • Syntax: =IFS(ISTEXT(A1), "Text", ISNUMBER(A1), "Number", TRUE, "Other")
      • Example: Categorizes the content in cell A1 as "Text," "Number," or "Other" using the IFS function based on whether it is text or a number.
      1. SUMPRODUCT with Multiple Criteria and Wildcards:
      • Syntax: =SUMPRODUCT((A1:A10="*apple*")*(B1:B10="Red"), C1:C10)
      • Example: Calculates the sum of values in column C where column A contains the word "apple" (using wildcards) and column B is "Red."
      1. IF with WORKDAY Function:
      • Syntax: =IF(WORKDAY(A1, 5)<TODAY(), "More than 5 workdays ago", "5 or fewer workdays ago")
      • Example: Checks if the date 5 workdays after the date in cell A1 is more than 5 workdays ago using the WORKDAY function within an IF statement.
      1. IFS with Logical Tests:
      • Syntax: =IFS(A1<0, "Negative", A1=0, "Zero", A1>0, "Positive")
      • Example: Categorizes the value in cell A1 as "Negative," "Zero," or "Positive" using the IFS function based on different logical tests.

Comments

Post a Comment

Popular posts from this blog

Lila's Journey to Becoming a Data Scientist: Her Working Approach on the First Task

-
  Education and Skill Acquisition: Lila, with an economics background, decides to transition to data science. Enrolls in the IBM Data Science Professional Certificate for comprehensive training. Gains proficiency in statistics, machine learning, and programming languages. Building a Strong Foundation: Develops a deep understanding of data manipulation and visualization using Python libraries. Visualization for Storytelling: Learns to create informative visualizations, aiding in effective communication of findings. Hands-On Experience: Engages in Kaggle competitions and personal data projects for practical experience. Establishes a GitHub profile to showcase her projects and skills. Data Wrangling and Preprocessing: Focuses on data cleaning, preprocessing, and handling missing data and outliers. Communication and Storytelling: Hones data storytelling skills using tools like Matplotlib and Plotly. Networking and Collaboration: Actively participates in data science communities, attend...
Read more

Notes on Hiring for Data Science Teams

-
  Unicorn Myth in Hiring: Companies often seek individuals with a myriad of skills for data science roles. Ideal candidates possess domain-specific knowledge, excel in data analysis, and have strong presentation and storytelling skills. However, finding such a "unicorn" is rare, and the focus should shift to other essential qualities. Resonance with Company DNA: The key is to identify applicants who resonate with the company's values and business domain. While analytics skills can be taught, passion for the business is intrinsic and contributes significantly to productivity. Curiosity Matters: Curiosity is a top priority in hiring for a data science team. Candidates should demonstrate a broad curiosity, showing interest in various aspects of their surroundings beyond just data science. Sense of Humor: A lighthearted approach is crucial, as being too serious may hinder collaboration and creativity. A sense of humor aids in navigating challenges and fosters a positive work ...
Read more

switch functions

-
  Nested IF Function : The  nested IF  function allows you to perform tests with multiple conditions and return different values based on the results of those tests. It is called “nested” because one IF function is embedded inside another IF function. Syntax of a generic nested IF formula: =IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4) ) ) Each subsequent IF function is embedded into the  value_if_false  argument of the previous function. SWITCH Function : The  SWITCH  function is a more compact alternative to nested IF statements. It compares an expression against a list of values and returns the result according to the first matching value. Syntax of the SWITCH function: =SWITCH(expression, value1, result1, [default or value2, result2], ... [default or valueN, resultN]) It has four arguments: expression : The value to compare against the list of values. valueN : A value com...
Read more