Advanced Excel: December 2011

Thursday, December 8, 2011

Sample final exam

I am not providing a sample for Lab-based questions, because that is extremely straightforward.

1) Given a range called data, give me a count of how many
numbers in the range are evenly divisible by 5. Use COUNTIF
or COUNTIFS.

2) Same question, but use DCOUNT.

3) Same question, but use Array formulas.

4) Given a range called data, use a deliberate circular reference to find the all-time high.

5) Make a column chart based on a table with three columns. (Salesman, Widgets Sold, Gadgets Sold).
Sever the "Widgets Sold" series from the actual spreadsheet.
Make the chart title depend on the contents of cell G19.

6) Consider the following balloon mortgage:
Term: 5-year balloon mortgage
Principal: $200,000
Interest rate: 6.5%
Monthly payment: $1,264.14
Remaining balance at end of 5-year term: $187,222

Know how to calculate any of these, if you are provided with the rest.

7) Find the shortest word in a range (restricted to a single column). Use MATCH and INDEX to accomplish this. Bonus points if you can perform it in a single array formula.

Tuesday, December 6, 2011

Final
Dec 22
4-6 PM

What chapters will the final be on?

Lab: 6, 7, 8
Lecture: 7,8,11,14,15,(EC:16,9),first section of 17 (charts)

reminders:
14 and 15 are really a single chapter
about array formulas.
Really, only focus on the counting and summing part of it.
that also means how to do AND and OR
in array formulas.
Overlap with Ch 7. SumIf, CountIf.

Ch 11 (financial formulas)