Even casual Excel users have probably encountered a situation where they wanted to test a formula against a range of possible input values. One way to do this is by simply copying a formula down a column or row of your table. A more structured way of doing this, though, is Excel's Data Tables (What-if Analysis) feature. Most K201 students don't usually have much trouble with figuring out how this feature works, but it can be easy to forget under the pressure of an exam.

Actually, the trickiest thing we cover in this video is defining custom data formats. We're not sure why, but K201 GPs and exams usually like to ask you to create custom data formats as a followup to two-variable data table questions.

One of the more powerful features of Excel is its collection of lookup functions, which let you cross-reference tabular data in your spreadsheets. K201 students sometimes find them tricky to master though, especially when it comes to determining when to use a `VLOOKUP`

, `HLOOKUP`

, or `INDEX-MATCH`

formula.

In this video for GP7, Dalia goes over examples for each of these formulas - you'll be done in no time!

Our last video for GP6 covers Excel's `IF`

formula. We go over some simple use cases (for GP6), as well as some more complicated applications. We'll talk about how `IF`

formulas can be used inside another `IF`

formula ("nested IFs") along with the `AND`

and `OR`

boolean functions to build decision trees.

Nested `IF`

statements tend to give K201 students a lot of trouble, so we hope this video can help clear things up a bit!

`PV`

(present value) function, planning a college fund with the `NPER`

formula, and computing the annual interest rate on a known payoff amount for an investment with `RATE`

.

An amortization schedule is typically used to describe compound-interest loans that have a fixed amount to be paid each period (often months) until the entire principal and interest have been paid off. It shows how the principal (initial amount borrowed) and interest accrued change from month to month, assuming that payments are made on time.

Most K201 students find the unit on amortization tables to be pretty easy. Once you've done one, you've done them all! Nevertheless, today we present two different problems that involve using the `PMT`

formula to construct an amortization table (if you're curious as to how the PMT formula actually works, see this article for an explanation of how to derive and solve the geometric series for amortizing loans.)

In the next video, Dalia will go over some of the other financial formulas covered by GP6.