Excel Chapter 7 Flashcards

Questions and Answers

Which function should you use to find the number of days until the end of the semester given today's date in B1 and the last day in B2?

=DAYS(B2,B1)

What function should you use to extract just 12 from the date stored in cell C1 (5/12/2018)?

=DAY(C1)

What function would produce the same results as =OR(B1='Available',B1='Limited') to show TRUE if the art is not sold out?

=NOT(B1='Sold Out')

What function should you use to identify the number of freshmen who are Information Systems majors without filtering the dataset?

DCOUNT Signup and view all the answers

In the MATCH function, which argument should contain the specific runner's time for a time of 4:05?

Lookup_value Signup and view all the answers

What function would you use to calculate the total interest paid for the first year of a mortgage?

CUMIPMT Signup and view all the answers

Where do you enter the range for the dataset when performing an advanced filter?

List range Signup and view all the answers

What value should be referenced for the per argument in the IPMT function to calculate interest on the last monthly payment at the end of the 15th year?

180 Signup and view all the answers

What function derives the correct answer for the fines given specific conditions of speeding and previous violations?

=IF(OR(B10-B5>=5,B11>=2), MAX(200,(B10-B5)*50),45) Signup and view all the answers

What function would you use to calculate the total number of periods in a loan or investment?

NPER Signup and view all the answers

Study Notes

Excel Date Functions

Use the =DAYS(B2,B1) function to calculate the number of days between today's date and the last day of the semester.
To extract the day from a date in cell C1 (e.g., May 12, 2018), utilize =DAY(C1).

Logical Functions

In an artwork availability list, utilize =NOT(B1="Sold Out") to display TRUE for artwork that is not sold out.

Counting Functions

Use DCOUNT to identify the number of freshmen who are Information Systems majors without filtering the dataset.

Lookup Functions

The MATCH function requires the specific runner's time to be placed in the lookup_value argument to determine their race placement.

Financial Functions

To calculate the total interest paid in the first year of a mortgage, use CUMIPMT.
Calculate the interest on the last payment of a $300,000 mortgage with a 5% APR at the end of the 15th year by using 180 as the value for the per argument in the IPMT function.

Conditional Functions

To set a speeding fine based on certain conditions (exceeding speed limit, prior tickets), use =IF(OR(B10-B5>=5,B11>=2), MAX(200,(B10-B5)*50),45) to calculate the correct amount based on set rules.

General Loan Functions

Use the NPER function to calculate the total number of periods for a loan or investment scenario.

Description

Test your knowledge with these flashcards covering key Excel functions related to date calculations. Learn how to effectively use the DAYS function and other related commands. Perfect for students mastering Excel in their studies.