In my initial Welcome post I made bold promises about creating budgets, which is one area that people from all walks of Excel experience would like to improve upon. So here we are and I haven't even thrown down the most important function for creating a sweet budget: SUMIF.
As its name implies, SUMIF will take a conditional sum. This means that in an array of numbers, SUMIF will take the sum of all the numbers that meet a specified criterion. In the case of the budget, which is what I'll focus on, you may have a list of expenses in one column and a category tag in another column that identifies the expense (Rent, Groceries, Drinking, etc.). Through intelligent use of this function, you can easily and accurately create a summary of your financial life (or aspirations if you're doing projections).
Let's dive into the SUMIF syntax (download the example worksheet here):
=SUMIF(range,criterion,[sum_range])
1. Range: The 'range' is the group of cells that will hold the condition. In our example above, it would be the column that contains the category tags.
2. Criterion: This is the value that the function is looking to match in the 'range'. So if you wanted to find the total of all of your "Drinking" expenses, your criterion would be "Drinking".
3. Sum Range: This is another group of cells that holds the numbers that you want to Sum. In the budget example, this would be the column that shows how much you have spent on each thing. Important note: the 'sum range' and 'range' should have the same dimensions and should line up perfectly. For example, if your 'range' is $A$2:$A$40, then your 'sum range' should be $B$2:$B$40 (assuming the numbers are in column B). This is good practice and keeps it clean.
Putting it all together: So the function takes your 'criterion', which we're saying is "Drinking" and will look cell by cell in the 'range' and will take the sum of every cell in the 'sum range' that lines up with a cell in the 'range' that says "Drinking". Importantly, the match has to be exact - if you put "Drinks" in one of the cells, the function will not recognize it and will skip over the row. So make sure to categorize expenses while you're sober and ready to be consistent.
As is customary at EfP, I will take a second to tell you to use references rather than text (like "Drinking"). Rather than write a different function for each of your expense categories, why don't you use a reference to a cell that contains the word "Drinking". This way you can write the function once and fill it down for all of your other expenses - if you have any other expenses. Remember: don't put " "s around references; mind your relative and absolute references (you will generally want your ranges to be absolute and your criterion to be relative); and DO put " "s around your criterion if it is not a reference.
You may have noticed above that the 'sum range' argument is in square brackets, indicating that it is optional. You are indeed a keen observer; the 'sum range' is optional in the case that it is identical to your 'range'. This will happen if you are looking, for example, to take the sum of only numbers that are greater than a certain number. In this case you are essentially looking for your 'criterion' in your 'sum range', and putting it in twice would be redundant. But it won't hurt to put it in. Unless anything other than perfect efficiency hurts you.
January 17, 2009
November 24, 2008
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.
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.
November 13, 2008
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.
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.
November 11, 2008
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.
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.
November 1, 2008
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.
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.
October 29, 2008
A Note about Levels:
Excel for Poets wants to make sure your knowledge-building quest is taking place in a reasonable order. Because I don't create posts in any rational order, I've tried to aid this process by tagging posts based on four "experience" or "degree of difficulty" levels. Here is my attempt to rationalize their meanings:
1. Beginner: You read the Welcome post and said, "yes, that's me, this is going to be awesome" (or, "yes, that's me, this blog is lame, but may actually come in handy"). You've probably encountered Excel, but you also probably struggled through making a chart for school and may not know how to add two cells together. The Beginner posts focus mainly on foundational concepts, such as filling and references that will be assumed knowledge in later posts. In addition to this, rudimentary functions will be addressed, but nothing fundamentally more complicated than the SUM function.
2. Intermediate: You either graduated the Beginner course or came in knowing the basics. You probably have some knowledge of Excel, but it probably isn't well-organized. By this I mean that you encountered a specific problem and either learned or figured out a specific solution without necessarily understanding the underlying logic of it. For people who consider themselves "intermediates" I would recommend taking a quick look at the beginner posts, just in case anything slipped through the cracks. In terms of content, the Intermediate lessons will probably be the most useful as a reference source. I will go through a large number of functions and demonstrate how to use them, and hopefully in realistic situations where they will be useful. The limit is that each function will be used in a plain vanilla, straightforward way.
3. Excel Jock (or just "Jock"): You know the functions, and came not to learn the basics, but to up your already formidable game. Maybe you came just to point out that there may be a better way to do something than the way I did it. In that case, I challenge you to a duel (and invite you to make a mocking, scathing comment). The Jock posts will broadly cover optimized spreadsheet design and accomplishing the goal of truly separating your 'hard-coded' or downloaded data from the badass calculation/presentation engine you've created. The name of the game will be flexibility in both data aggregation and location through aggressive use of conditionals, power lookups, conditional sums, and maybe dynamic ranges (haven't decided if this is Jock level or . . .
4. All-Madden: BOOM! You're Jocky enough to get voted to the All-Star team. The All-Madden posts will predominately explore the use of Array formulas to establish complete and utter supremacy over your data. In addition to arrays, there will be some arbitrary level at which a Jock formula might become a little too monstrous and move into the All-Madden category. Things such as embedded dynamic ranges and multiple OFFSETS might qualify. I also want to make it clear that I will not be covering any VBA or macro stuff, and all the functions will work in either Excel 2007 or 2003. There will also be scant coverage of graphs/charts and pivot tables (even though they're totally awesome). We'll see how it goes.
1. Beginner: You read the Welcome post and said, "yes, that's me, this is going to be awesome" (or, "yes, that's me, this blog is lame, but may actually come in handy"). You've probably encountered Excel, but you also probably struggled through making a chart for school and may not know how to add two cells together. The Beginner posts focus mainly on foundational concepts, such as filling and references that will be assumed knowledge in later posts. In addition to this, rudimentary functions will be addressed, but nothing fundamentally more complicated than the SUM function.
2. Intermediate: You either graduated the Beginner course or came in knowing the basics. You probably have some knowledge of Excel, but it probably isn't well-organized. By this I mean that you encountered a specific problem and either learned or figured out a specific solution without necessarily understanding the underlying logic of it. For people who consider themselves "intermediates" I would recommend taking a quick look at the beginner posts, just in case anything slipped through the cracks. In terms of content, the Intermediate lessons will probably be the most useful as a reference source. I will go through a large number of functions and demonstrate how to use them, and hopefully in realistic situations where they will be useful. The limit is that each function will be used in a plain vanilla, straightforward way.
3. Excel Jock (or just "Jock"): You know the functions, and came not to learn the basics, but to up your already formidable game. Maybe you came just to point out that there may be a better way to do something than the way I did it. In that case, I challenge you to a duel (and invite you to make a mocking, scathing comment). The Jock posts will broadly cover optimized spreadsheet design and accomplishing the goal of truly separating your 'hard-coded' or downloaded data from the badass calculation/presentation engine you've created. The name of the game will be flexibility in both data aggregation and location through aggressive use of conditionals, power lookups, conditional sums, and maybe dynamic ranges (haven't decided if this is Jock level or . . .
4. All-Madden: BOOM! You're Jocky enough to get voted to the All-Star team. The All-Madden posts will predominately explore the use of Array formulas to establish complete and utter supremacy over your data. In addition to arrays, there will be some arbitrary level at which a Jock formula might become a little too monstrous and move into the All-Madden category. Things such as embedded dynamic ranges and multiple OFFSETS might qualify. I also want to make it clear that I will not be covering any VBA or macro stuff, and all the functions will work in either Excel 2007 or 2003. There will also be scant coverage of graphs/charts and pivot tables (even though they're totally awesome). We'll see how it goes.
October 28, 2008
Getting Our Hands Dirty with Absolute and Relative References
Fill it Down!
Now that we've gone through some of the basics of what kind of information Excel can handle (if you don't know what I'm talking about, then check out this post), let's dive into making your sheets sing. I talk a lot - and will continue to, despite all your protests - about how the beauty of Excel is its elegance and flexibility. Even though it may not be as appealing as a gymnast with fine, discerning taste, a well-constructed sheet can help you save time and look good while doing it.
What I generally mean by flexibility and elegance is that a single function can be quite general and do exactly what you want it to do in a variety of situations. The task of 'Filling' is central to this ability. 'Fill Down' is one of the most important and basic tasks you can do on a spreadsheet. The idea is that you write a formula in one cell, and then instead of writing it over and over again (or copying and pasting it, which can also work), you can just drag it down as far as you want and it will perform its calculations.
For example, let's say you have two columns of numbers that you want to multiply together in a third column. You can do that very easily by performing the following:
In the first cell, I typed the formula to multiply 11 by 4. After hitting Return to finish the formula, I used the 'fill handle' (the little cross in the bottom right corner) to drag the formula down so that it would perform the same calculation for the whole range. In addition to filling down, you can also use the fill handle to fill to the right (very common), left (unusual), and up (highly unusual).
Absolute vs. Relative References
There is an inherent ambiguity to the idea of filling. When you drag that formula down, how does Excel know that you don't want to multiply 11 by 4 in all of the cells? Maybe you do want to do that. Maybe you want to multiply each of the cells on the right by 11 (the first cell on the left). This is where the distinction between absolute and relative references comes in, so listen up.
By default, Excel assumes you want to use relative references. This means that Excel interprets the formula I put in the third column above as this, "Two cells to the left times one cell to the left." This way, as I fill down, the interpretation remains the same, but the values themselves change. If I used absolute references, Excel would think, "B3 times C3". In this case, as I fill down, the interpretation and the values remain the same, because no matter where it goes, B3 times C3 equals the same amount. If I changed the value in one of those two cells, it would change the product in every cell I filled in.
You can also use combinations, such as "B3 times one cell to the left". As you fill down, this would multiply each cell in the right column by the top value in the left column. If you changed a value in the right column, it would only affect only one product; however, if you changed the value in cell B3, it would change every product.
Now that we've gone through what it means, here's what it looks like in Excel:
Relative reference: B3
Absolute reference: $B$3
It's that simple, dolla dolla bills. You can type them in by hand, or after you type in B3 you can hit the f4 key on your keyboard, which is probably a quicker way to do it.
Last Step in this Mammoth Post: Splitting the Cell
Each reference, rather than being a whole unit, is actually the combination of two smaller units, the Row Reference and the Column Reference. As you may have guessed, you can hold one of them Absolute and make the other Relative. Here's the list of the four reference combinations:
1. Relative Row, Relative Column (default): B3
2. Relative Row, Absolute Column: $B3
3. Absolute Row, Relative Column: B$3
4. Absolute Row, Absolute Column: $B$3
In general, the Relative/Absolute nature of the Row reference will determine how the formula will behave when you fill it up or down and the Relative/Absolute nature of the column reference will determine how it behaves when you fill left or right. The Absolute and Relative references sheet will allow you to get your hands dirty with this knowledge and try a sick challenge set.
Next up for the beginners, we deal with our first real functions. Who's pumped?
Now that we've gone through some of the basics of what kind of information Excel can handle (if you don't know what I'm talking about, then check out this post), let's dive into making your sheets sing. I talk a lot - and will continue to, despite all your protests - about how the beauty of Excel is its elegance and flexibility. Even though it may not be as appealing as a gymnast with fine, discerning taste, a well-constructed sheet can help you save time and look good while doing it.
What I generally mean by flexibility and elegance is that a single function can be quite general and do exactly what you want it to do in a variety of situations. The task of 'Filling' is central to this ability. 'Fill Down' is one of the most important and basic tasks you can do on a spreadsheet. The idea is that you write a formula in one cell, and then instead of writing it over and over again (or copying and pasting it, which can also work), you can just drag it down as far as you want and it will perform its calculations.
For example, let's say you have two columns of numbers that you want to multiply together in a third column. You can do that very easily by performing the following:
In the first cell, I typed the formula to multiply 11 by 4. After hitting Return to finish the formula, I used the 'fill handle' (the little cross in the bottom right corner) to drag the formula down so that it would perform the same calculation for the whole range. In addition to filling down, you can also use the fill handle to fill to the right (very common), left (unusual), and up (highly unusual).
Absolute vs. Relative References
There is an inherent ambiguity to the idea of filling. When you drag that formula down, how does Excel know that you don't want to multiply 11 by 4 in all of the cells? Maybe you do want to do that. Maybe you want to multiply each of the cells on the right by 11 (the first cell on the left). This is where the distinction between absolute and relative references comes in, so listen up.
By default, Excel assumes you want to use relative references. This means that Excel interprets the formula I put in the third column above as this, "Two cells to the left times one cell to the left." This way, as I fill down, the interpretation remains the same, but the values themselves change. If I used absolute references, Excel would think, "B3 times C3". In this case, as I fill down, the interpretation and the values remain the same, because no matter where it goes, B3 times C3 equals the same amount. If I changed the value in one of those two cells, it would change the product in every cell I filled in.
You can also use combinations, such as "B3 times one cell to the left". As you fill down, this would multiply each cell in the right column by the top value in the left column. If you changed a value in the right column, it would only affect only one product; however, if you changed the value in cell B3, it would change every product.
Now that we've gone through what it means, here's what it looks like in Excel:
Relative reference: B3
Absolute reference: $B$3
It's that simple, dolla dolla bills. You can type them in by hand, or after you type in B3 you can hit the f4 key on your keyboard, which is probably a quicker way to do it.
Last Step in this Mammoth Post: Splitting the Cell
Each reference, rather than being a whole unit, is actually the combination of two smaller units, the Row Reference and the Column Reference. As you may have guessed, you can hold one of them Absolute and make the other Relative. Here's the list of the four reference combinations:
1. Relative Row, Relative Column (default): B3
2. Relative Row, Absolute Column: $B3
3. Absolute Row, Relative Column: B$3
4. Absolute Row, Absolute Column: $B$3
In general, the Relative/Absolute nature of the Row reference will determine how the formula will behave when you fill it up or down and the Relative/Absolute nature of the column reference will determine how it behaves when you fill left or right. The Absolute and Relative references sheet will allow you to get your hands dirty with this knowledge and try a sick challenge set.
Next up for the beginners, we deal with our first real functions. Who's pumped?
Subscribe to:
Posts (Atom)
Posts
