Week 1's Quiz

Test your knowledge of basic formulas, conditional logic, and lookup functions.

Q1. SUM & Multiplication

You need total revenue from a single-row record where Unit Price is in E2 and Quantity is in F2. Which formula is correct?

A) =SUM(E2*F2) B) =E2*F2 C) =SUM(E2,F2) D) =SUM(E2:F2)

Q2. COUNT vs COUNTA

Column A has Product IDs (text) and blank cells. You want to count how many products are listed. Which function?

A) =COUNT(A2:A100) B) =COUNTA(A2:A100) C) =COUNTIF(A2:A100,">0") D) =SUM(A2:A100)

Q3. IF for Stock Status

Quantity is in F2 and Reorder Level in G2. Which formula flags Reorder when stock is below the level?

A) =IF(F2>G2,"Reorder","OK") B) =IF(F2


          
            
            C) =IF(F2=G2,"Reorder","OK")
          
          
            
            D) =IF(G2-F2,"Reorder","OK")


        
        
        Answer: B) =IF(F2

        Explanation: Reorder is needed when Quantity (F2) is less than Reorder Level (G2).



    
      
      Q4. VLOOKUP with Table Example
      Find the Supplier Name for Product ID PRD-015 from this table:
      
        
          
            Product ID Product Name Category Supplier Name
          
          
            PRD-014 Camera Electronics SUP-A
            PRD-015 Headphones Electronics SUP-B
            PRD-016 Jacket Clothing SUP-C
          
        
      
        Assume the table range is A2:D100 with Product ID in column A (1st column), Product Name in column B (2nd column), Category in column C (3rd column), and Supplier Name in column D (4th column). Which formula correctly returns the Supplier Name for Product ID "PRD-015"?
        
          
            
            A) =VLOOKUP("PRD-015",A2:D100,2,FALSE)
          
          
            
            B) =VLOOKUP("PRD-015",A2:D100,3,FALSE)
          
          
            
            C) =VLOOKUP("PRD-015",A2:D100,4,FALSE)
          
          
            
            D) =VLOOKUP("PRD-015",A2:D100,1,FALSE)
          
        
        
        
        Answer: C) =VLOOKUP("PRD-015",A2:D100,4,FALSE)
        Explanation: Supplier Name is in the 4th column of the range; use column index 4 with exact match (FALSE).
        
    

    
      
      Q5. COUNTIFS for Multiple Conditions
      Count Electronics products that also need Reorder (Stock Status column I). Which formula?
        
          
            
            A) =COUNTIF(C2:C51,"Electronics")
          
          
            
            B) =COUNTIF(I2:I51,"Reorder")
          
          
            
            C) =COUNTIFS(C2:C51,"Electronics",I2:I51,"Reorder")
          
          
            
            D) =COUNTIFS(I2:I51,"Reorder",C2:C51,"")
          
        
        
        
        Answer: C) =COUNTIFS(C2:C51,"Electronics",I2:I51,"Reorder")
        Explanation: COUNTIFS applies AND logic across ranges; both category and status must match.
        
    

    
      
      Q6. AVERAGEIF for Category Price
      Average Unit Price for category in cell A10 (categories listed in C2:C51, prices in E2:E51). Which formula?
        
          
            
            A) =AVERAGE(E2:E51)
          
          
            
            B) =AVERAGEIF(E2:E51,A10,C2:C51)
          
          
            
            C) =AVERAGEIF(C2:C51,A10,E2:E51)
          
          
            
            D) =AVERAGEIFS(E2:E51,C2:C51,A10)
          
        
        
        
        Answer: C) =AVERAGEIF(C2:C51,A10,E2:E51)
        Explanation: AVERAGEIF(criteria_range, criteria, average_range) averages prices where category matches A10.
        
    

    
      
      Q7. SUMPRODUCT for Category Value
      Total inventory value for Electronics (Category in C, Unit Price in E, Quantity in F). Which formula?
        
          
            
            A) =SUMIF(C2:C51,"Electronics",E2:E51)
          
          
            
            B) =SUMPRODUCT((C2:C51="Electronics")*(E2:E51)*(F2:F51))
          
          
            
            C) =SUM(E2:E51*F2:F51)
          
          
            
            D) =SUMIFS(E2:E51,C2:C51,"Electronics",F2:F51,"Electronics")
          
        
        
        
        Answer: B) =SUMPRODUCT((C2:C51="Electronics")*(E2:E51)*(F2:F51))
        Explanation: SUMPRODUCT multiplies price and quantity only for rows where category matches, then sums the results.
        
      

      
      
        Q8. MIN and MAX Functions
        You want to find the lowest unit price in range E2:E51. Which formula is correct?
        
          
            
            A) =MINIMUM(E2:E51)
          
          
            
            B) =MIN(E2:E51)
          
          
            
            C) =SMALL(E2:E51,1)
          
          
            
            D) Both B and C are correct
          
        
        
        
          Answer: D) Both B and C are correct
          Explanation: MIN(E2:E51) and SMALL(E2:E51,1) both return the minimum value. MIN is more straightforward for this purpose.
        
      

      
      
        Q9. SUMIF Function
        Sum all quantities (F2:F51) where the category (C2:C51) is "Electronics". Which formula?
        
          
            
            A) =SUMIF(C2:C51,"Electronics",F2:F51)
          
          
            
            B) =SUMIF(F2:F51,C2:C51,"Electronics")
          
          
            
            C) =SUMIF(C2:C51,F2:F51,"Electronics")
          
          
            
            D) =SUMIF("Electronics",C2:C51,F2:F51)
          
        
        
        
          Answer: A) =SUMIF(C2:C51,"Electronics",F2:F51)
          Explanation: SUMIF syntax is SUMIF(criteria_range, criteria, sum_range). The criteria range comes first, then the criteria, then the range to sum.
        
      

      
      
        Q10. Nested IF Function
        Create a formula that returns "High" if quantity (F2) > 100, "Medium" if > 50, otherwise "Low". Which is correct?
        
          
            
            A) =IF(F2>100,"High",IF(F2>50,"Medium","Low"))
          
          
            
            B) =IF(F2>50,"Medium",IF(F2>100,"High","Low"))
          
          
            
            C) =IF(F2>100,"High","Medium",IF(F2>50,"Low"))
          
          
            
            D) =IF(F2>50,"Low",IF(F2>100,"High","Medium"))
          
        
        
        
          Answer: A) =IF(F2>100,"High",IF(F2>50,"Medium","Low"))
          Explanation: Check the highest threshold first (100), then the next (50), then default to "Low". Order matters in nested IF statements.
        
      

      
      
        Q11. Absolute Cell Reference
        You want to copy a formula from B2 that multiplies A2 by a tax rate in cell $C$1. Which formula in B2 ensures C1 stays fixed when copied down?
        
          
            
            A) =A2*C1
          
          
            
            B) =A2*$C$1
          
          
            
            C) =A2*$C1
          
          
            
            D) =$A$2*C1
          
        
        
        
          Answer: B) =A2*$C$1
          Explanation: $C$1 is an absolute reference that locks both row and column, so it won't change when copied. A2 is relative and will adjust.
        
      

      
      
        Q12. COUNTIF with Wildcards
        Count products in C2:C51 where the category name starts with "Elect". Which formula?
        
          
            
            A) =COUNTIF(C2:C51,"Elect")
          
          
            
            B) =COUNTIF(C2:C51,"Elect*")
          
          
            
            C) =COUNTIF(C2:C51,"*Elect")
          
          
            
            D) =COUNTIF(C2:C51,"Electronics")
          
        
        
        
          Answer: B) =COUNTIF(C2:C51,"Elect*")
          Explanation: The asterisk (*) is a wildcard that matches any characters. "Elect*" matches any text starting with "Elect".
        
      

      
      
        Q13. VLOOKUP Approximate Match
        You have a lookup table with discount thresholds. For a purchase amount in A10, you want to find the discount rate using approximate match. The lookup table is in E2:F5 (amounts in E, rates in F), sorted ascending. Which formula?
        
          
            
            A) =VLOOKUP(A10,E2:F5,2,FALSE)
          
          
            
            B) =VLOOKUP(A10,E2:F5,2,TRUE)
          
          
            
            C) =VLOOKUP(A10,E2:F5,1,TRUE)
          
          
            
            D) =VLOOKUP(A10,F2:E5,2,TRUE)
          
        
        
        
          Answer: B) =VLOOKUP(A10,E2:F5,2,TRUE)
          Explanation: TRUE enables approximate match (or omit it). Column index 2 returns the discount rate. The table must be sorted ascending for approximate match to work correctly.
        
      

      
      
        Q14. SUMIFS with Multiple Criteria
        Sum quantities (F2:F51) where category (C2:C51) is "Electronics" AND unit price (E2:E51) is greater than 50. Which formula?
        
          
            
            A) =SUMIFS(F2:F51,C2:C51,"Electronics",E2:E51,">50")
          
          
            
            B) =SUMIFS(F2:F51,E2:E51,">50",C2:C51,"Electronics")
          
          
            
            C) =SUMIF(C2:C51,"Electronics",F2:F51)*SUMIF(E2:E51,">50",F2:F51)
          
          
            
            D) Both A and B are correct
          
        
        
        
          Answer: D) Both A and B are correct
          Explanation: SUMIFS applies AND logic to all criteria. The order of criteria pairs doesn't matter, as long as each pair is (criteria_range, criteria).
        
      

      
      
        Q15. VLOOKUP Error Handling
        You use VLOOKUP to find a product, but some product IDs don't exist. You want to show "Not Found" instead of #N/A. Which formula wraps the VLOOKUP?
        
          
            
            A) =IFERROR(VLOOKUP(A2,D2:E100,2,FALSE),"Not Found")
          
          
            
            B) =IF(VLOOKUP(A2,D2:E100,2,FALSE)="#N/A","Not Found",VLOOKUP(A2,D2:E100,2,FALSE))
          
          
            
            C) =VLOOKUP(A2,D2:E100,2,FALSE) OR "Not Found"
          
          
            
            D) =ERROR(VLOOKUP(A2,D2:E100,2,FALSE),"Not Found")
          
        
        
        
          Answer: A) =IFERROR(VLOOKUP(A2,D2:E100,2,FALSE),"Not Found")
          Explanation: IFERROR catches any error (including #N/A) and returns the specified value. This is cleaner than checking for specific error types.

Product ID	Product Name	Category	Supplier Name
PRD-014	Camera	Electronics	SUP-A
PRD-015	Headphones	Electronics	SUP-B
PRD-016	Jacket	Clothing	SUP-C

Data Analysis with Spreadsheet Program

Week 1's Quiz

Q1. SUM & Multiplication

Q2. COUNT vs COUNTA

Q3. IF for Stock Status

Q4. VLOOKUP with Table Example

Q5. COUNTIFS for Multiple Conditions

Q6. AVERAGEIF for Category Price

Q7. SUMPRODUCT for Category Value

Q8. MIN and MAX Functions

Q9. SUMIF Function

Q10. Nested IF Function

Q11. Absolute Cell Reference

Q12. COUNTIF with Wildcards

Q13. VLOOKUP Approximate Match

Q14. SUMIFS with Multiple Criteria

Q15. VLOOKUP Error Handling