Taking Excel out on a Date

Why on earth would I write a post entirely about using dates in Excel? Simply because Excel is not it's usual solid self when dealing with these vixens. Many of you, I'm sure have typed in something like "6/20/2008" into a cell and had no trouble. But did you know that Excel took your little date and turned it into the non-sensical number "38157"? Huh? Better yet, try typing "38157" into a cell and then formatting it into a date. How do you feel about Excel's handling of dates now?

For reasons beyond my comprehension, Excel uses "serial numbers" as its system for delivering dates. So long as these numbers are formatted as dates, everything is fine. But let's say you want to play around with dates a little bit, how do you deal with these serial numbers?

I would divide Date-related functions into two primary buckets: Date Construction and Date Extraction.

The primary Date Construction function is aptly named DATE(year, month, day). As you can see, it takes three arguments: a year, a month, and a day. So, going with 6/20, we would type in DATE(2008,6,20), which will return our desired date. In sum, the DATE function will construct a serial number for the date that corresponds to the year, month and day you feed it.

The three Date Extraction formulas are YEAR(serial number), MONTH(serial number), DAY(serial number). These formulas extract information from a serial number. Keeping with our example:
1: YEAR(38157) = 2008
2: MONTH(38157) = 6
3: DAY(38157) = 20

Okay, that's great, now why do we care? Well, since you ask, there are myriad applications for Date functions. In the linked worksheet, we will look at two, both related to budget applications. The first uses date extraction formulas, which will come in handy when we cover the SUMIF formula. The second uses date construction formulas, and includes my favorite way to automatically determine the last day of the month using a formula that is not related to "30 days has September, April, June and November."

Download the Dates worksheet here. If you have trouble with the formulas on the Date Worksheet tab, I put the answers on the "Date Answers" tab.

Note: This worksheet introduces the concept of Nesting Functions (a function as an argument of another function). In date-related examples, this means starting with one serial number and using both extraction and construction formulas at the same time to create a new serial number. So if we started with 6/20/2008 in cell a1 and wanted to create a formula to give us 7/20/2008 in cell b1, we would put the following nested formula in b1:

=DATE(YEAR(a1),MONTH(a1)+1,DAY(a1)).

Excel will first calculate the internal functions, so:

YEAR(a1) = 2008

MONTH(a1) = 6

DAY(a1) = 20

Which leaves us with:

=DATE(2008,6+1,20)

=DATE(2008,7,20)

and voila! we get 7/20/2008.

Another important note for this: If you put in a zero for the "day" argument in the DATE function, it will return the last day of the previous month. So, if you wrote: =DATE(2008,2,0), the function would return "1/31/2008". This trick comes in handy when you want to create a list of dates that correspond to the last, rather than the first day of the month.

Grade School Cont'd: More Arithmetic

Thought you graduated? Well, you're almost there, but let's roll through some more arithmetic before you move on to bigger and badder things like conditionals. You know how to add numbers in Excel (if you're thinking: "I do?", then check out this post), now it's time to subtract, multiply, divide, and raise to powers.

In the addition post, we explored in excruciating detail the differences between using an Operator and a Function to accomplish your task. In this installment, we'll do the same for the other arithmetic activities in a less excruciating fashion. Download "Arithmetic" right here to start exploring.

Here is your Operators starting five:
+ Addition
- Subtraction
* Multiplication
/ Division
^ Raise to Power

Here is your Function starting four:
SUM Addition
PRODUCT Multiplication
QUOTIENT Division
POWER Raise to Power

If you've read any other posts, you'd probably assume I'm just going to talk about how awesome Functions are, and how they should be used in all cases to solve arithmetic problems. You would then be surprised to learn that in some cases Operators are actually better than Functions (even though it breaks my hear). If you take a look at the Arithmetic sheet, you'll see which functions are good, and which one is absolute garbage.

Before we close up shop on this brief post, here is a broad run-down of how arithmetic works in Excel, for those of you who haven't heard the term "Order of Operations" in more than twenty years. Order of Operations is PEMDAS, Please Excuse My Dear Aunt Sally, or whatever mnemonic you favor to remember that it goes: Parentheses, Exponents, Multiplication, Division, Addition Subtraction. Excel adheres to this rule and all of the other fun rules/properties you don't remember (associative, commutative, um . . .). Por Ejemplo:

(2+3)*4 = 20
2+3*4=14
(2+3)^2*4=100 (had to verify that one)

Things can get confusing with parentheses, because in addition to being used for mathematical operations, they also are involved in functions. So if you did some hybrid function, such as:

=SUM((2+3)^2,(3+4)*2)

Excel will solve it like this:

=SUM(5^2,7*2)
=SUM(25,14)
=39

Make sense? If not, play around in the worksheet and try out some different things. Remember, instead of using hard-coded numbers like I did here, do it using references as I do in the worksheet. Also, if the ideas aren't sticking, I recommend going back to the addition post, which really lays out the concepts underpinning this somewhat cursory explanation of arithmetic functions in Excel.

Way Back to School: Simple Addition Excel Style

Welcome back to grade school. Unfortunately, you will receive no virtual gold star stickers for good performance, but on the bright side, you will not have to sit in timeout or miss recess if you don't do your homework. You are old enough and motivated enough to do your homework for the sake of self-betterment, right? RIGHT?

Before getting too far along, let's download the "Addition" worksheet here.

With that we move on to adding numbers together. Excel provides two basic ways of adding number together: the operator '+', and the function SUM. I'm assuming that you, the reader, know how to add two numbers together (if not, no recess for you), thus I am going to focus on how to transfer your knowledge to the spreadsheet.

Operators:
Using an operator is the most intuitive way to perform arithmetic functions. You basically write it as you see it:

Excel: = 2 + 5
Your Brain's version: "what is 2 plus 5?"

Okay, so maybe it's a bit different. The '=' sign always goes in the front (even though the formula in your head is "2 plus 5 equals 7"), I cannot stress this point enough. Without the '=', Excel assumes you are just writing "2 + 5" for the fun of it. And trust me, you are not writing "2 + 5" for the fun of it. This form is intuitive because it's almost exactly as you would type it into a simple calculator (which they didn't tell you about until middle school). Again, with the difference that the '=' goes at the beginning, rather than the end.

Functions:
Okay, this is not quite as intuitive a translation, but is more along the lines of how you do things in Excel's language. Let's try the same example:

Excel: =SUM(2,5)
Your brain's version: What is the sum of the following numbers: 2 and 5?

This version is more efficient than the previous translation when dealing with a Series of numbers. A Series is a list of numbers or references, separated by commas. The SUM function can take a Series as its Argument. An Argument is the information you feed into a function, that allows it to come up with a solution. You can also call Arguments "Parameters", which is probably more accurate, but also more scary, so I will stick with the word Argument. For example, if you just wrote SUM(), Excel would answer with a blank stare, because it does not know the numbers you want to sum up. However, if you type =SUM(2,5), Excel knows that you want to add 2 and 5 together, and smiles at you. The SUM function takes a variable number of Arguments, depending on how many numbers you want to add together. Most of the Arithmetic functions work this way, but most of the more complicated functions take a fixed number of Arguments. This may not make too much sense right now, but don't worry about it, it will become clear over time what exactly we mean by an Argument.

In addition to a Series, SUM can also take a Range as an argument. A Range is a continuous group of references. These are examples of Ranges:

1. A1:A4 (includes cells A1, A2, A3, A4)
2. A1:C3 (includes cells A1, A2, A3, B1, B2, B3, C1, C2, C3)

Notes about Ranges:
1. Always Rectangular: The first reference defines the top left corner of the range, the colon signals that you are creating a range, and the second reference defines the bottom right corner. Excel takes this information and creates the associated rectangle.

2. Can use Absolute or Relative References: For a review of how these references work, check out this post. This is only important if you intend to fill your function or copy and paste it (which I always encourage). $A$1:$A$4 will always refer back to those cells, no matter where you fill or copy and paste the function. This is not true of A1:A4, which is a relative range, and will move around if you start filling or copying and pasting your function.

For example, if you put =SUM(A1:A4) in cell A5, and then fill it down to cell A6, you will get a different answer, because Excel translates your function to =SUM(A2:A5) in A6, which will be larger than =SUM(A1:A4). In contrast =SUM($A$1:$A$4) will always take the sum of those four numbers, no matter where you put it.

You can also use any sort of combination of relative and absolute references mentioned in the aforementioned post.

Back to SUM
The biggest advantage of SUM over '+' is that SUM can take a Range as an Argument. So if you want to add all the numbers in Column A from Row 1 to Row 100, instead of typing each number, which would be spectacularly awful, you can simply do =SUM(A1:A100) and let your buddy Excel do the rest. Don't you wish you knew how to use it in third grade?

In most circumstances, you will want to Sum a Range. I encourage you to do your own scratchwork on the worksheet I provide above, just to get used to doing it. Because that's what you would have had to do in grade school.

Turbocharge your VLOOKUP with MATCH

Did you look at the VLOOKUP post and think, "Well, that's nice, but something about that col_index_number argument seems a bit crappy?" If so, you are DEFINITELY ready for the Jock curriculum. If this thought didn't cross your mind, that is perfectly reasonable; please read on.

Because Excel data tables are two dimensional (rows by columns), lookup functions are also two dimensional. VLOOKUP is dynamic in the vertical (rows) dimension - based on the lookup_value, the VLOOKUP will return different answers. As you change the value in the reference cell, the answer will change.

But what if you want to make it dynamic horizontally? The simple VLOOKUP is static horizontally, based on the col_index_number argument, which takes a simple number. As discussed, this number tells the function how many columns to the right the information you are seeking is located. What if instead of counting columns on your data table each time you write a VLOOKUP, you would rather let Excel do the work? This is where the MATCH function comes in handy.

The MATCH function finds the relative position of a value within a range. The reason it works so well with the VLOOKUP is that MATCH returns a simple number, which is exactly what VLOOKUP wants for the col_index_number argument. Here is what the function looks like:

MATCH(lookup_value, lookup_array, match_type)

For the lookup_value, you feed a reference for the value that you're looking to match, just as with VLOOKUP. The lookup_array is just like table_array in VLOOKUP, but instead of a large rectangle, the range will be only one row tall. The match_type argument is similar to the range_lookup in VLOOKUP, except instead of "false", we use the number zero for an exact match (the reason for this will make at least a little bit of sense in a later post when we deal with approximate matches, but for now, let's look for exact matches).

Tips:

1. The trickiest part of using MATCH with VLOOKUP is the lookup_array argument. The col_index_number will tell the VLOOKUP how many columns to the right to look relative to the first column. MATCH returns the number that represents how many columns to the right the lookup_value is, relative to the range you create. In order for this to work, the first column in both ranges must be the same. If your VLOOKUP table_array starts in column C, your MATCH range needs to start in column C, or else your results will be horrendously wrong.

2. I take it back, the trickiest part of using MATCH with VLOOKUP is getting your relative and absolute references working correctly. Because you are going to fill the function both down and across, things are going to get a bit messy. For review on using absolute and relative references, check out this post.

Let's kick that electricity analysis up a notch and take a look at some trends over time. Download VLOOKUP with MATCH here.