Excel Functions and References Quiz

Questions and Answers

Which function is best for applying a set of rules that cannot be calculated by a formula?

=IF function

=PMT function

=VLOOKUP function (correct)

simply by using appropriate formulas like '=C2 * 5' or whatever is necessary

None of the above

What value will be returned by the function =IF(B6>C6,C6A1,C6A2) given that B6=10, C6=20, A1=2, A2=3?

60 (correct)

How will the entry =B$3 appear when copied to a cell two columns to the right and five rows down?

=D$3 (correct)

=$B6

=B$3

=D$6

Which of the following is an example of an absolute cell reference?

$C$4 Signup and view all the answers

If cell D15 contains the formula =$C$5*D15, what type of reference is D15 in the formula?

Circular Reference Signup and view all the answers

How was the formula in H5 ultimately entered correctly?

=F5/$F$12 Signup and view all the answers

Which formula was used in cell I5 to calculate the total admission fees?

=F5*B$14 Signup and view all the answers

To use text in an IF function, it must be enclosed in:

Double quote marks Signup and view all the answers

What's the formula of B17?

=AVERAGE(B8:B12) Signup and view all the answers

What's the formula of B16?

=SUM(B8:B12) Signup and view all the answers

What's the formula of D19?

=MIN(D8:D12) Signup and view all the answers

In a VLOOKUP function, what is the lookup value?

Is a reference to a cell containing a value to look up. Signup and view all the answers

The outcome of the IF statement =IF((B10+5)>C10,B10,C10) where cell B10=5 and cell C10=19 is?

19 Signup and view all the answers

Which formula would give the monthly car payment if I purchased a car for $15,000 at 1.9% interest for 3 years?

=PMT(0.019/12,36,-15000) Signup and view all the answers

While using formulas, ___________ error is displayed in the formula bar when a cell reference is not valid.

=#REF! Signup and view all the answers

In the =PMT function, the PV is:

the present value, or amount, of the loan. Signup and view all the answers

You'll never break even if you have...

negative profit per period Signup and view all the answers

If a course percentage is in cell S4, what is wrong with this VLOOKUP statement when displaying the student's letter grade?

There are no absolute references for V13:W17. Signup and view all the answers

Study Notes

VLOOKUP Function

VLOOKUP function is used to apply specific rules to multiple cells in Excel, ideal for scenarios like commission structures.
Primary choices for similar tasks include IF function, PMT function, and direct formulas.

IF Function Examples

The formula =IF(B6>C6,C6*A1,C6*A2) evaluates the values in cells B6 and C6, applying different formulas depending on the condition.

Absolute and Relative References

A formula like =B$3 retains the row reference when copied vertically but adjusts the column reference.
Absolute cell reference is denoted by $, such as $C$4, ensuring the reference does not change when copied.

Circular References

A circular reference occurs when a formula refers to its own cell, e.g., =$C$5*D15 in D15.

Correct Formula Usage

Correct formula entry examples include =F5/$F$12 and =F5*B$14, demonstrating proper use of absolute and mixed references.

Text in IF Functions

Text used in IF functions must be enclosed in double quote marks for proper handling.

Common Functions

AVERAGE and SUM functions are frequently used to compute averages and totals, such as =AVERAGE(B8:B12) and =SUM(B8:B12).

Lookup Value in VLOOKUP

The lookup value in a VLOOKUP function is a reference to a cell containing a value to search for within a specified range.

IF Statement Outcomes

The IF statement =IF((B10+5)>C10,B10,C10) evaluates to either the value in B10 or C10 based on the condition provided.

PMT Function

The PMT function calculates loan payments, requiring inputs like interest rate and number of periods, e.g., =PMT(0.019/12,36,-15000) for monthly payments on a car loan.

Error Handling

A #REF! error indicates an invalid cell reference in a formula, signalling issues with referenced cells.

Present Value in PMT Function

In the PMT function, PV (present value) refers to the total amount of the loan, which affects the payment calculation.

Factors Affecting Profitability

Negative profit per period can prevent a business from breaking even, highlighting a crucial aspect of financial analysis.

VLOOKUP Challenges

Using VLOOKUP requires ensuring proper references; for instance, if a grading table lacks absolute references, it could fail to accurately reflect lookups based on input values.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge of key Excel functions like VLOOKUP and IF. This quiz covers absolute and relative references, circular references, and proper formula usage. Prepare to apply specific rules in Excel for practical scenarios.