QBA 1720 Exam 2: Key Concepts & Excel Functions

Posted on Jan 25, 2025 in Computers

QBA 1720 Exam 2 Note Sheet

Key Concepts and Formulas

  1. Measures of Central Tendency:

    • Mean (Average): =AVERAGE(range)

    • Median: =MEDIAN(range)

    • Mode: =MODE.SNGL(range)

  2. Measures of Variability:

    • Standard Deviation: =STDEV.S(range)

    • Variance: =VAR.S(range)

    • 68-95 Rule:

      1. 68% of data is within 1 standard deviation.

      2. 95% of data is within 2 standard deviations.

  3. COUNTIFS Function:

    • Counts based on multiple conditions: =COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)

    • Example: Count the number of students who scored above 70 in Exam 1: =COUNTIFS(A1:A100,">=70")

  4. SUMIFS Function:

    • Sums values based on multiple conditions: =SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)

    • Example: Sum total sales for products with a price over $100: =SUMIFS(B1:B100,A1:A100,">=100")

  5. AVERAGEIFS Function:

    • Averages values based on multiple conditions: =AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)

    • Example: Find the average salary of managers over 50 years old: =AVERAGEIFS(B1:B100,A1:A100,"manager",C1:C100,">=50")

  6. Pivot Tables:

    • Used to summarize data dynamically by categorizing it.

    • Steps:

      1. Select your data.

      2. Go to Insert → PivotTable.

      3. Set up rows, columns, and values for your desired summary.

  7. Scatterplots and Line Charts:

    • Scatterplot:

      1. Select two columns of data and insert a scatterplot to see relationships.

      2. Use the Insert Scatter (X, Y) Chart option.

    • Line Chart (for time series data):

      1. Select your data and insert a line chart.

  8. Descriptive Statistics Toolpak:

    • Provides summary statistics (mean, median, mode, etc.) for a dataset:

      1. Go to Data → Data Analysis → Descriptive Statistics.

      2. Select your range and ensure “Summary Statistics” is checked.

      3. This method is not dynamic (doesn’t auto-update).

Key Excel Functions for QBA 1720

FunctionFormulaDescription
1. SUM=SUM(A1:A10)Adds all numbers in the range A1 to A10.
2. AVERAGE=AVERAGE(B1:B10)Calculates the average of numbers in B1 to B10.
3. COUNT=COUNT(C1:C10)Counts the number of cells with numbers in C1 to C10.
4. MAX=MAX(D1:D10)Returns the largest number in the range D1 to D10.
5. MIN=MIN(E1:E10)Returns the smallest number in the range E1 to E10.
6. IF=IF(F1 > 100, "Above 100", "100 or below")Returns one value if a condition is true and another if false.
7. IFERROR=IFERROR(G1/H1, "Error")Returns a custom message if an error occurs in the formula.
8. IFS=IFS(I1 > 90, "A", I1 > 80, "B", I1 > 70, "C")Returns a value corresponding to the first true condition.
9. COUNTIF=COUNTIF(J1:J10, "Yes")Counts cells in J1 to J10 that contain “Yes”.
10. SUMIF=SUMIF(K1:K10, ">100", L1:L10)

Adds values in L1

where corresponding values in K1

meet the criteria.

11. AVERAGEIF=AVERAGEIF(M1:M10, ">50", N1:N10)

Calculates the average of N1

for cells in M1

that are greater than 50.

12. AVERAGEIFS=AVERAGEIFS(O1:O10, P1:P10, ">50", Q1:Q10, "<100")

Calculates the average of O1

based on multiple criteria.

13. VLOOKUP=VLOOKUP(R1, S1:U10, 2, FALSE)

Looks for a value in the first column of a range and returns a

value in the same row from a specified column.

14. HLOOKUP=HLOOKUP(T1, U1:Z10, 2, FALSE)

Similar to VLOOKUP, but searches for a value in the first row

of a range.

15. CONCATENATE=CONCATENATE(AA1, " ", AB1)Joins text in AA1 and AB1 with a space.
16. TEXT=TEXT(AC1, "0.00")Formats the number in AC1 as text with two decimal places.
17. DATE=DATE(2024, 10, 22)Returns a specific date (e.g., October 22, 2024).
18. DATEDIF=DATEDIF(AD1, AE1, "D")Calculates the difference in days between dates AD1 and AE1.
19. TODAY=TODAY()Returns the current date.
20. NOW=NOW()Returns the current date and time.