Tutoring at Indiana University (IU) in Bloomington, Indiana

Finite math, K201, calculus, and statistics explained

Today, we're releasing the first of our K201 Excel series videos.

In this how-to video, Dalia explains date functions, including today, yesterday, future dates, and date differences (datedif). While easy to understand, these concepts are also easily forgettable. We recommend brushing up on these functions once or twice before an exam.

Download the exercise files

Stay tuned, we'll be posting new videos every week for the rest of the semester. If you need additional help, reach out to us via our tutoring request page! We'd be happy to help.

Today we're releasing a double feature on GP5. In this first video, we're explaining COUNT, SUM, AVERAGE, MAX, MIN. These concepts, also called aggregate functions, are pretty easy to understand. So, in part two we'll step up our game with some IFs.

Download the exercise files

Second video of today's double feature: GP5: COUNTIF, SUMIF, AVERAGEIF. These concepts are easy when first learned but as time goes on, and students learn more complicated formulas, they seem to always forget that the answer is simple. If your struggling with GP5, make sure and catch up with our other two GP5 videos, including COUNT, SUM, AVERAGE, MAX, MIN and last week's video, DATEDIFs.

The exercise spreadsheet that accompanies this video is the same as the one for part 2.

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.

Download the exercise files

In this video, we cover some of the other common financial formulas that come up in K201's Excel unit. Examples include comparing investment options using the 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.

Download the exercise files

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!

Download the exercise files

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!

Download the exercise files

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.

Download the exercise files

To be honest, Excel's text functions are kind of awkward and limited. Unlike full-blown programming languages like Python and Javascript, you don't have access to regular expressions or standard string functions such as split, join, and replace. Instead, you have to make due with complicated combinations of Excel's LEFT, RIGHT, MID, LEN, FIND, and CONCATENATE formulas to manipulate text.

In this video, we walk you through a common GP9 string (text) manipulation problem that asks you to use these functions to split, format, and rearrange text into more useful representations.

Download the exercise files

Dalia strikes again with this handy reference guide for the Microsoft Access portion of K201 at IU Bloomington. This is not intended as a complete introduction to using Access. It assumes that you have the basics down, and tries to cover the specific topics where K201 students tend to get stuck the most often.

Dates

Quarters

Quarter Months Expression
Quarter 1 January, February, March Between #1/1/2016# and #3/31/2016#
Quarter 2 April, May, June Between #4/1/2016# and #6/30/2016#
Quarter 3 July, August, September Between #7/1/2016# and #9/30/2016#
Quarter 4 October, November, December Between #10/1/2016# and #12/31/2016#

Other Date Functions

Date Expression
Today's date Date()
Yesterday Date()-1
Tomorrow Date()+1
3 months from now Date()+90

How many days are in a month?

I do the knuckle trick!

Totals Row

Whenever you see multiple rows of potentially the same data you need to turn on your totals row.

Turn unnecessa...

Continue Reading...

Today's post is courtesy of our awesome K201 tutor Dalia, who wants you to get comfortable with some of Excel's more common lookup and indexing functions. This is one of the hardest parts of Excel for most people, and where students tend to get stuck in the class.

One of the more powerful features of Excel is the ability to automatically cross-reference a table of values based on some input value(s). For example, you might have a spreadsheet that computes your company's total revenue from each customer, but to do that it must look up the hourly rate and total number of billed hours for each customer from another spreadsheet. When used like this, spreadsheets can almost (but not quite) be used like the relational databases you studied in the first half of K201.

The most common functions you'll use for cross-referencing in Excel are:

  • VLOOKUP and HLOOKUP
  • MATCH
  • INDEX

Both VLOOKUP and HLOOKUPs are used in the same way, so from now on we'll just refer to them collectiv...

Continue Reading...