Sample midterm 2

Date and Time:
1) Using just arithmetic, give me a date 5 years from now. (Pretend there are no leap years.)

2) Do the same using data and time functions.

Counting and Summing:

Generate a list of Salespeople and the number of Widgets they sold, and total revenue they brought in.
1) Count how many people sold between 8 and 23 widgets. Use summing functions. (e.g. sumif, countif)
2) Do the same using an array formula

Do the same based on length of salesperson's name (between 4 and five letters long)
1) using summing funcs
2) using array formulas

Lookup functions:
Look up for me Bob's revenue and #widgets sold, from same table

financial functions
If I borrow 1 million for 100 years, at 7% APR, compounded quarterly, with a balloon payment of half a million, what will be my payment?


If I borrow 1 million for 100 years, with a PMT of $1000 monthly, compounded quarterly, with a balloon payment of half a million, what will be my APR? What will be my annual effective rate?


VBA question:
write a UDF that takes in a range and returns the square root of the sum of all the elements in the range
make a macro that will color all values in a range between 5 and 50 the background color red.