Skip to content
Home » Forum

Forum

Advanced Excel form...
 
Notifications
Clear all

Advanced Excel formulas and conditional statements

1 Posts
1 Users
0 Reactions
79 Views
Mark Sikaundi
(@emmanuelmark117)
Member Admin
Joined: 2 years ago
Posts: 99
Topic starter  

Here’s a detailed explanation of some advanced Excel formulas and conditional statements:

Advanced Excel Formulas

  1. INDEX and MATCH:

    • INDEX: Returns the value of an element in a table or an array, selected by the row and column number indexes.
    • MATCH: Searches for a specified item in a range of cells and then returns the relative position of that item in the range.
    • Usage Example: Finding the price of a product in a table:
      excel

       
      =INDEX(B2:B10, MATCH(E1, A2:A10, 0))

      Here, E1 contains the product name. A2:A10 is the range where the product names are listed, and B2:B10 is the range where the corresponding prices are listed.

  2. VLOOKUP and HLOOKUP:

    • VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
    • HLOOKUP: Searches for a value in the first row of a table and returns a value in the same column from a specified row.
    • Usage Example:
      excel

       
      =VLOOKUP("Product1", A1:D10, 3, FALSE)

      This searches for "Product1" in the first column of A1:D10 and returns the value from the 3rd column of the matching row.

  3. SUMIF and SUMIFS:

    • SUMIF: Adds the cells specified by a given condition or criteria.
    • SUMIFS: Adds the cells specified by multiple conditions or criteria.
    • Usage Example:
      excel

       
      =SUMIF(A2:A10, "Apples", B2:B10)

      This adds up all values in B2:B10 where the corresponding value in A2:A10 is "Apples".

      excel

       
      =SUMIFS(C2:C10, A2:A10, "Apples", B2:B10, ">10")

      This adds up all values in C2:C10 where the corresponding value in A2:A10 is "Apples" and B2:B10 is greater than 10.

  4. IF and Nested IF:

    • IF: Checks whether a condition is met, and returns one value if true, and another value if false.
    • Nested IF: Uses multiple IF statements within one formula to check for multiple conditions.
    • Usage Example:
      excel

       
      =IF(A1 > 10, "High", "Low")

      This checks if the value in A1 is greater than 10. If true, it returns "High"; otherwise, it returns "Low".

      excel

       
      =IF(A1 > 10, "High", IF(A1 < 5, "Low", "Medium"))

      This checks multiple conditions. If A1 is greater than 10, it returns "High". If A1 is less than 5, it returns "Low". Otherwise, it returns "Medium".

  5. COUNTIF and COUNTIFS:

    • COUNTIF: Counts the number of cells that meet a single criterion.
    • COUNTIFS: Counts the number of cells that meet multiple criteria.
    • Usage Example:
      excel

       
      =COUNTIF(A2:A10, "Oranges")

      This counts the number of cells in A2:A10 that contain "Oranges".

      excel

       
      =COUNTIFS(A2:A10, "Oranges", B2:B10, ">10")

      This counts the number of cells in A2:A10 that contain "Oranges" and in B2:B10 that have a value greater than 10.

  6. AVERAGEIF and AVERAGEIFS:

    • AVERAGEIF: Returns the average of cells that meet a single criterion.
    • AVERAGEIFS: Returns the average of cells that meet multiple criteria.
    • Usage Example:
      excel

       
      =AVERAGEIF(A2:A10, "Bananas", B2:B10)

      This returns the average of values in B2:B10 where A2:A10 is "Bananas".

      excel

       
      =AVERAGEIFS(C2:C10, A2:A10, "Bananas", B2:B10, ">5")

      This returns the average of values in C2:C10 where A2:A10 is "Bananas" and B2:B10 is greater than 5.

  7. TEXT Functions:

    • LEFT, MID, RIGHT: Extract text from a string.

      excel

       
      =LEFT(A1, 3)

      This returns the first 3 characters from the text in A1.

      excel

       
      =MID(A1, 2, 4)

      This returns 4 characters from the text in A1, starting from the 2nd character.

      excel

       
      =RIGHT(A1, 3)

      This returns the last 3 characters from the text in A1.

    • CONCATENATE (or CONCAT): Joins several text strings into one.

      excel

       
      =CONCATENATE(A1, " ", B1)

      This joins the text in A1 and B1 with a space in between.

      excel

       
      =CONCAT(A1, " ", B1)

      This does the same using the newer CONCAT function.

    • TEXT: Formats a number and converts it to text.

      excel

       
      =TEXT(A1, "0.00")

      This converts the number in A1 to text, formatted with two decimal places.

  8. ARRAY Formulas:

    • Example:
      excel

       
      {=SUM(A1:A10*B1:B10)}

      This multiplies each element in A1:A10 by the corresponding element in B1:B10 and sums the results. Note the curly braces {} which indicate it's an array formula.

 

Conditional Statements

  1. Conditional Formatting:

    • Usage: Apply different formats (like colors) to cells that meet certain criteria.
    • Example:
      • Highlight cells greater than 100:
        excel

         
        Home > Conditional Formatting > Highlight Cells Rules > Greater Than > 100
      • Apply a color scale based on cell values:
        excel

         
        Home > Conditional Formatting > Color Scales > Choose a color scale
  2. IF with AND/OR:

    • Usage: Combine multiple conditions within an IF statement.
    • Example:
      excel

       
      =IF(AND(A1 > 10, B1 < 5), "Good", "Bad")

      This returns "Good" if A1 is greater than 10 and B1 is less than 5. Otherwise, it returns "Bad".

      excel

       
      =IF(OR(A1 > 10, B1 < 5), "Good", "Bad")

      This returns "Good" if either A1 is greater than 10 or B1 is less than 5. Otherwise, it returns "Bad".

  3. ISNUMBER and ISTEXT:

    • Usage: Check if a value is a number or text.
    • Example:
      excel

       
      =IF(ISNUMBER(A1), "Number", "Not a Number")

      This returns "Number" if A1 is a number, otherwise, it returns "Not a Number".

      excel

       
      =IF(ISTEXT(A1), "Text", "Not Text")

      This returns "Text" if A1 is text, otherwise, it returns "Not Text".

  4. IFERROR:

    • Usage: Handle errors in formulas.
    • Example:
      excel

       
      =IFERROR(A1/B1, "Error")

      This returns "Error" if A1/B1 results in an error (like division by zero).

Practical Exercise Questions:

  1. Basic Formulas:

    • Calculate the Total Cost for each product.
    • Calculate the Final Cost for each product after applying the discount.
  2. Conditional Formatting:

    • Highlight the rows where the Payment Status is "Unpaid".
    • Apply a three-color scale to the Final Cost column to differentiate high, medium, and low values.
  3. Data Validation:

    • Set a validation rule to allow only numbers between 0 and 20 in the Discount (%) column.
    • Create a dropdown list for the Supplier column with the options: Supplier A, Supplier B, Supplier C.
  4. VLOOKUP:

    • Use VLOOKUP to populate the Contact Info for each supplier in a new column based on a separate table of supplier contact details.
  5. SUMIF/SUMIFS:

    • Calculate the total Final Cost for each supplier using the SUMIF function.
  6. Pivot Tables:

    • Create a Pivot Table to summarize total Quantity Bought, Total Cost, and Final Cost by Supplier.
  7. Charts: Certainly! Here’s a continuation with the practical exercise questions and some sample data you can use:

Practical Exercise Questions (Continued):

  1. Charts:

    • Create a bar chart to visualize the total Quantity Bought for each product.
    • Create a pie chart to show the distribution of Total Cost by Supplier.
  2. Sorting and Filtering:

    • Sort the table by Final Cost in descending order.
    • Filter the table to show only products with Quantity Bought greater than 300.

Sample Data for Exercises

Here’s the detailed Excel sheet structure:

ID Product Quantity Bought Price per Unit ($) Total Cost ($) Payment Status Discount (%) Final Cost ($) Payment Date Supplier
1 Wheat 500 1.50 750.00 Paid 5 712.50 2024-01-10 Supplier A
2 Corn 300 2.00 600.00 Unpaid 10 540.00 2024-01-15 Supplier B
3 Soybeans 400 1.75 700.00 Paid 0 700.00 2024-01-20 Supplier A
4 Rice 600 1.60 960.00 Paid 15 816.00 2024-01-25 Supplier C
5 Barley 450 1.40 630.00 Unpaid 5 598.50 2024-01-30 Supplier A
6 Oats 350 1.55 542.50 Paid 8 499.10 2024-02-05 Supplier B
7 Canola 200 2.20 440.00 Paid 12 387.20 2024-02-10 Supplier C
8 Cotton 150 1.80 270.00 Unpaid 10 243.00 2024-02-15 Supplier A
9 Sorghum 100 1.65 165.00 Paid 5 156.75 2024-02-20 Supplier B
10 Sunflower 250 2.50 625.00 Unpaid 20 500.00 2024-02-25 Supplier C
11 Sugarcane 300 1.90 570.00 Paid 7 530.10 2024-03-01 Supplier A
12 Peanuts 220 2.10 462.00 Unpaid 15 392.70 2024-03-05 Supplier B
13 Tobacco 180 2.30 414.00 Paid 10 372.60 2024-03-10 Supplier C
14 Alfalfa 400 1.50 600.00 Paid 5 570.00 2024-03-15 Supplier A
15 Hay 500 1.60 800.00 Unpaid 10 720.00 2024-03-20 Supplier B
16 Flaxseed 280 1.70 476.00 Paid 12 419.20 2024-03-25 Supplier C
17 Rye 320 1.45 464.00 Unpaid 8 426.88 2024-03-30 Supplier A
18 Hops 150 2.40 360.00 Paid 10 324.00 2024-04-05 Supplier B
19 Millet 400 1.35 540.00 Paid 5 513.00 2024-04-10 Supplier C
20 Lentils 300 1.85 555.00 Unpaid 10 499.50 2024-04-15 Supplier A

Example Detailed Explanation and Steps

  1. Basic Formulas:

    • Total Cost:
      • In cell E2, enter: =C2*D2
      • Drag the fill handle from E2 to E21 to apply the formula to the other rows.
    • Final Cost:
      • In cell G2, enter: =E2-(E2*F2/100)
      • Drag the fill handle from G2 to G21 to apply the formula to the other rows.
  2. Conditional Formatting:

    • Highlight Unpaid Payments:
      • Select the range F2
         

        .

      • Go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
      • Enter "Unpaid" and choose a format.
    • Three-Color Scale for Final Cost:
      • Select the range G2
         

        .

      • Go to Home > Conditional Formatting > Color Scales > More Rules.
      • Set the lowest value color (e.g., green), midpoint value (e.g., yellow), and highest value color (e.g., red).
  3. Data Validation:

    • Discount % Validation:
      • Select the range F2
         

        .

      • Go to Data > Data Validation.
      • Set Allow to "Whole number" and between 0 and 20.
    • Supplier Dropdown:
      • Select the range J2
         

        .

      • Go to Data > Data Validation.
      • Set Allow to "List" and Source to "Supplier A, Supplier B, Supplier C".
  4. VLOOKUP:

    • Supplier Contact Info:
      • Create a separate table (e.g., in columns K and L) listing suppliers and their contact info.
      • In a new column, use VLOOKUP to find the contact info for each supplier.
      excel

       
      =VLOOKUP(J2, $K$2:$L$10, 2, FALSE)
      • Drag the fill handle to apply the formula.
  5. SUMIF/SUMIFS:

    • Total Final Cost per Supplier:
      • Use SUMIF to calculate the total final cost for each supplier.
      excel

       
      =SUMIF(J2:J21, "Supplier A", G2:G21)
  6. Pivot Tables:

    • Create a Pivot Table:
      • Select your data range and go to Insert > PivotTable.
      • Drag Supplier to Rows, Product to Columns, and Final Cost to Values.
  7. Charts:

    • Bar Chart:
      • Select the data range for products and quantity.
      • Go to Insert > Bar Chart.
    • Pie Chart:
      • Select the data range for suppliers and total cost.
      • Go to Insert > Pie Chart.
  8. Sorting and Filtering:

    • Sort by Final Cost:
      • Select the range A1
         

        .

      • Go to Data > Sort.
      • Sort by Final Cost in descending order.
    • Filter by Quantity:
      • Select the range A1
         

        .

      • Go to Data > Filter.
      • Apply a filter to the Quantity Bought column to show only values greater than 300.
This topic was modified 3 months ago by Mark Sikaundi

   
Quote
Share: