Business K201
Mar
07,
2017

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.

Business K201
Mar
21,
2017

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.

Business K201
Mar
22,
2017

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.

Business K201
Mar
26,
2017

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.

Business K201
Mar
28,
2017

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`

.

Business K201
Mar
30,
2017

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!

Business K201
Apr
04,
2017

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!

Business K201
Apr
17,
2017

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.

Business K201
Apr
23,
2017

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.

Business K201
Oct
16,
2016

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.

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#` |

Date | Expression |
---|---|

Today's date | `Date()` |

Yesterday | `Date()-1` |

Tomorrow | `Date()+1` |

3 months from now | `Date()+90` |

I do the knuckle trick!

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

Turn unnecessa...

Business K201
Aug
05,
2016

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 `HLOOKUP`

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