# Bloomington Tutors Blog Finite math, K201, calculus, and statistics explained

## GP5 - Combining Index/Match with Aggregate Functions

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.

## GP5 - Aggregate functions (COUNT, SUM, AVERAGE, MAX, MIN, COUNTIF, SUMIF, AVERAGEIF) in Microsoft Excel

Today we're releasing a double feature on GP5. In this video, we start by explaining COUNT, SUM, AVERAGE, MAX, MIN. These concepts, also called aggregate functions, are pretty easy to understand. We'll then move on to COUNTIF, SUMIF, and AVERAGEIF, which are slightly more difficult but will definitely make an appearance in GP5.

## GP5 How to do date functions (TODAY,DATEDIF) and more complex formulas

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.

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.

## 12 key concepts you should master before your first K201 lab practical (Access)

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...

## When to use INDEX-MATCH, VLOOKUP, or HLOOKUP

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...