In a previous blog, I walked you through 5 easy excel functions that have the potential to save educators a lot of time and energy. Since then, I’ve had a number of people ask me to show some more Excel functions that are very helpful as well! These 5 are definitely more advanced than the ones I shared previously, but are super cool and much more fun! Again, I’ll break each one down one by one; however, to understand what is happening I’m also going to have to get in to concepts that might be intimidating. And if you are easily intimidated by the explanations and just want a summary, just suck it up, buttercup… we can push through this!
Before I get to the formulas, we need to explain something. Most of these formulas use a concept in Excel called arrays. Again, don’t panic! Arrays are easy, an array is just a list. In fact, we’ve already used arrays in the previous post!! When you select a column or a field, say A1:A22, you are selecting an array; a list of cells. There are three main ways to use arrays in Excel:
1) It can be a range like a column, row, or table.
2) It can be a CSE (ctrl-shift-enter) formula.
3) It can be an array constant, which is a list of values encased in curly brackets {}.
4) We know and understand number 1 because if you use Excel, you use that all the time.
So what is a CSE formula? CSE, shorthand for Ctrl-Shift-Enter, is a method to turn your formula into an array formula. Array formulas are used for 2 main purposes (there are others, but we are already too deep as it is): 1) to calculate and display into multiple cells simultaneously, and 2) to calculate FROM multiple cells simultaneously. We will be utilizing the second of these two. When you finish your formula, instead of hitting Enter, use the key combination Ctrl-Shift-Enter, otherwise you could get an Error. NOTE: Excessive use of CSE formulas will slow down your spreadsheet considerably.
Array formula allows you to process a certain operation on multiple values using a single function. In other words, we can perform some calculation on more than one value without doing it manually on each cell.
What about an array constant? Array Constants, ={x,y,z}, are primarily used to create and manipulate several different values at once and often independently of each other.
I encourage you to learn more about arrays on the wide interwebs. While having this base knowledge will help in the why of the following formulas, you can just follow the descriptions and not learn about the why if you want. With that said, this post will differ from the previous Excel one. This time I’m going to give the how first in ways that are easy to understand. Then I’ll describe why the first 2 of these work at the end. That way, if you don’t care about the why, you can just check out the how and go about your day. But where’s the fun in that?
1) =SUMPRODUCT(1/COUNTIF(range,range&””))
OMG, you’re starting with a nested formula? Yes, yes I am. What the heck does this even do? Well, have you ever had a list of student IDs and YOU KNOW that some of the names are duplicated? You can’t count the list using COUNT, COUNTA, or even COUNTIF! Why? Because you have no way of accounting for the duplicate entries. What this formula does is count the number of unique entries within a range. Let’s break it down like I did on the previous post:
sumproduct(1/countif(A,B&””))
A: The range I want to Count
B: The same range as A
Let’s look at this in action. On the spreadsheets below, we have 12 student names as you can see by the regular COUNTA formula. But hold on… at least a few of those names are entered multiple times! Look what happens when we use our magical formula:
Only 7 names! We’ve successfully gotten rid of the duplicates. You can stop here if you don’t care about the why, but just make sure that the range is the same for both of the arguments.
2) =LOOKUP(2,1/(1-ISBLANK(value)),range)
Another nested function? You’ve got to be kidding me! Nope, again, this is a really easy function to use without understanding what’s actually happening. What this function does is allows you to dynamically display the last non-empty cell in a column or row even if you enter something after the current last entry. We’ll break it down in English like before:
Lookup(2,1/(1-isblank(A)),B)
A: The range from which you want to display the last entry
B: The same range as A
To demonstrate this, let’s look at the following example. What if I wanted to know the most recently entered score for a specific student, but I didn’t want to have to dig through all the data, I just wanted it on a pretty, easily viewable summary sheet? To do this, we can utilize the function above:
Case 1:
It’s easy to see that the most recent test is the test at the bottom and it displays 99. Now, I’m not going to change the formula at all, I’m just going to add another number at the end of the list:
It’s the same formula but it changed the number! Again, there is a semi-complicated explanation at the end, but if all you want to do is use it, here it is! So, if you ever want to display the last value of a column on a summary sheet even if other cells get entered (or other people enter data) then it’s all-good, it will automatically update.
RELATED: 5 Excel Functions Every Educator Needs to Know
3) =COUNTIFS(range,criteria,range2,criteria2,etc.)
Wait a minute… this looks familiar, but it’s spelled differently. You’re right! We’ve encountered a similar function in the previous blog post called COUNTIF, however sometimes you want to Count something when you have MORE THAN 1 criteria and COUNTIF just won’t cut it. Enter: COUNTIFS. For example, let’s say that you want to count all the students who are 9th graders AND those 9th graders are Male. When we try COUNTIF, we find that there isn’t a way to do that, instead excel has a relatively new function called COUNTIFS. OK, before I get to the “how do I use it part,” we need to talk about two concepts, logical AND and logical operators. First off, this formula (and the next) work like AND statements. Logical AND means that EVERY Criteria has to be TRUE in order to get a result. In the case of my previous Count example, I only want a result when a student is BOTH in 9th grade AND is male. Both of those criteria have to be true in order for me to count the student. Make sense? Really with these formulas you can have up to 127 different range/criteria pairs! But remember that in order for you to get a result (so in the case of count, in order to count the student) ALL of the range/criteria pairs have to be met. Second, what are logical operators? Logical operators are the symbols used when you want to compare 2 or more things and return a true statement. For example, maybe I only want to count a 9th grade male if they have 3 or more absences, then one of my parameters will be looking at absences greater or equal to 3. There are six basic operators: Equal To (=), Not Equal To (<>), Greater Than (>), Less Than (<), Greather Than Or Equal To (>=), and Less Than or Equal To (<=). To learn more about logical operators, the interwebs are full of useful info.
Now, let’s get to how we use it! As always, the English version:
=countifs(A,B,C,D,etc.)
A: The 1st range you want to count
B: The 1st criteria you want to use to count that exists in A
C: The second range you want to count if the student is counted because of A and B
D: The second criteria to count that exists in C
etc.: You can continue having up to 127 range/criteria pairs
This might take a little explanation in a sort-of real world scenario. OK, let’s take a look at the example below. Let’s say we want to Count the total number of 9th grade females.
First off, what do we notice? We see that we are smart by using separate cells that our formula is referencing in case we wanted to change our parameters. In this case, we are using G3 as our grade level and H3 as our gender. So what am I doing here? OK, I’m looking at Column D2:D11 (the grade column) and saying if my grade in G3 (in our case 9) exists AND I’m looking at Column B2:B11 (the Gender column) and saying if my gender in H3 also exists, then I want to count the number of students that meet both criteria. And I get 3. I have 3 9th grade girls. Obviously with this example, we could have just counted, but what if we had 10,000 students? Let’s try another example. What if I wanted to count the number of 9th grade girls who have 3 or more infractions? In that case, my formula would look like this:
=COUNTIFS(D2:D11,G3,B2:B11,H3,E2:E11,">3")
Notice that the criterion with the operator is in quotes. For this blog I won’t offer an explanation, just remember that this is the appropriate syntax, otherwise you’ll get an error.
4) =SUMIFS(sum_range,range,critera,range2,criteria2,etc.)
Again, we’ve seen this before so don’t be alarmed! All we are doing is taking the previously learned concept and adding it to the SUMIF function we learned about in the last blog post. Everything else applies (AND statements, logical operators, etc.) so let’s take a look at the formula in English:
=sumifs(A,B,C,D,E,etc.)
A: What you are adding together
B: The 1st range you want to use
C: The 1st criteria you want to use to sum that exists in B
D: The second range you want to use if the student is TRUE because of B and C
E: The second criteria to use that exists in D
etc.: You can continue having up to 127 range/criteria pairs
Alright, again for all of you visual people out there, let’s take a look at the formula in action! What if I wanted to add together all of the infractions for my 9th grade girls? Well, let’s take a look.
We see that it is the same as COUNTIFS, except that the very first argument is column E2:E11. Basically this is telling Excel, “these are the numbers I want to add if my criteria are met.” In this case, my criteria are the same as COUNTIF. I’m looking for a grade in column D that matches the grade I have in cell G3 and ALSO I’m looking for a Gender in column B that matches the gender I have in H3. So I end up getting the total number of infractions for all of my 9th grade girls. And you’re done! Pretty easy, right?
5) {=INDEX(range,MATCH(criteria1&criteria2&etc1,range1&range2&etc2,0))}
Remember last time we encountered the index/match function it was like a super VLOOKUP? Well, this is like letting a radioactive computer bite the index/match function giving it super powers. Let’s back up a little and take a look at the original Index/Match function. If you recall, this allowed you to match a value from any column on one spreadsheet with a value to any column on another spreadsheet without having to know silly things like column number, or making sure your match parameter is in the first column of your table. You could just use the function to display all the relevant data points on one sheet. GENIUS! But what if you had more than one match criteria and you’re pulling all your data from one giant sheet that has everything? (Sense a theme here? ;p). Normally you can’t, but TA-DA… now you can! This version of Index/Match allows you to match against multiple criteria at the same time! NOTE: There are other ways to solve this, but this is probably one of the easier ones. Let’s break it down in English to understand:
{=index(A,match(B&C&etc1,D&E&etc2,0))}
A: The range from which you want to pull the data
B: The first criteria that you want to match
C: The second criteria that you want to match
D: The range where you want to match B against
E: The range where you want to match C against
etc1: Additional criteria you want to match (the more you match, the slower it will be)
etc2: Additional ranges you want to match against
What we’re telling excel is to return whatever is in range(columns usually) A and display it in my cell if both B and C exist at the same time in columns D and E. Let’s look at this visually. Let’s say that I want to display a specific test for a specific student on a sheet. In the screen shots below, I have two spreadsheets. The first is the sheet where I want the data and organized the way I want it. The second is the sheet where the data exists in one giant jumbled mess. I want to display only Test 2 for each student on my first sheet. (BTW, the first spreadsheet I’ve called Summary and the second spreadsheet I’ve called testdata)
That’s an annoying problem! Well, watch what happens when I use the formula:
Poof! The correct test score automatically appears. I’m matching BOTH the student ID and the phrase “Test 2” from my TestData sheet and displaying whatever the value in Column E is on the TestData sheet in this cell. For this one, I’m not going into the why it works however there are 2 things that you might notice. 1) the “curly cue brackets” ({}) surround the function. This is what tells Excel to treat this formula as an array formula (more commonly called a CSE formula) and you WILL get an error if you don’t use them. However, you can’t just enter them in, you have to do a specific key stroke where you hold down the Ctrl and Shift buttons and then hit Enter (hence… CSE). The second thing you might notice is the $ in cell F1 in my formula. This tells Excel that the cell is a constant value and shouldn’t be changed. More on that in the bonus.
There you go! 5 moderate to hard excel functions that I’ve found to be helpful. To see an explanation of why some of these are working the way they are (it’s a little like sorcery how the sumproduct function works) please scroll to the bottom and read. Be mindful of the warning though. Now, a bonus!
BONUS: Constant Reference (also called Fixed Reference or Absolute Reference)
Ever notice how Excel thinks it’s really smart by automatically iterating (meaning it’s adding to) the reference when you copy or drag down to fill in a row? And yes, you DO want it to change its reference for SOME of your variables, but not for all of them. To combat this, you use a simple expression called a constant reference. You do this by placing a $ sign in front of the column letter and another $ sign in front of the row number. You can also use a shortcut by using the F4 button on a PC or the key stroke combination Command-T on a Mac.
Oh No!!
Oh yes!
******WARNING WARNING******
The next part can get a little complicated. It goes in to the underlying explanation of each function we talked about earlier (but doesn’t really go into the nitty gritty… think about that after you read this and come back to me if you’re still interested in the crazier things happening)
=SUMPRODUCT(1/COUNTIF(range,range&””))
OK, so what is actually happening? This is a very tricky formula that is taking advantage of how Excel treats arrays. Remember that an array is just a list and in this case, our list is the range. Let’s look at the example below to get our heads around it.
We’ll work from the inside out. Countif is taking 2 arguments (remember =countif(range,criteria)). In this case, the arguments are both the same range. So, I want to look in the range to count the number of times a range exists. But Excel treats the second instance of the range as an array and is considering each cell of the range individually in an array constant. Why? Because the second argument that countif is looking for is a value, not a range. OK, so what is happening then? In our example, you can see what we typed in, but what Excel sees is sumproduct(1/(countif(range,{Johnny,Juan,Sally,Beth,…}&””)). Remember those curley cue brackets? That’s the array constant that we talked about before (we’ll get to that pesky &”” later). What ends up happening then, is that countif will count the total entries and then display the total for each time that value exists. For example, since Johnny appears three times in the list, you would get {…,3,…,3,3,…} each time Johnny’s name pops up. Make sense? Now let’s see what we get: sumproduct(1/{}). The next step is to use the 1 as the numerator in our division formula. If a name is in our range only once, then it will show 1/1 or 1. If a name is in our list twice, there will be two 1/2’s or 0.5s. In our example, Sally whose name appears 4 times would appear in our formula as sumproduct({…,.25,.25,.25,.25,…}). Make sense? What is this sumproduct thing then? Sumproduct takes 1 or more arrays and then multiplies each array and finally sums those results together. If there is only 1 array (like in our case) it adds the array together. So in this case, it’s adding together all the numbers, which gives us our total unique entries because each fraction will always end up summing together as 1.
But hold on, you didn’t cover the &”” we saw earlier? It’s an easy trick. The &”” is added to the range to account for any blank spots. Remember that we are creating an array of fractions: 1/1, 1/2, 1/3, etc. But when excel encounters a blank cell then it treats it as having a count of 0. Can we have 1/0? No! We’ll get the dreaded #DIV/0 error. To account for that, adding the &”” tells Excel, “Hey! I want to include (&) the blanks (“”).” That’s pretty much it for your time with this formula!
=LOOKUP(2,1/(1-ISBLANK(range)),range)
This is also a tricky function because, again, it uses the way that Excel treats arrays to trick the formula into doing something else. We’ll have to break this down again into its constituent parts: lookup(lookup_value,lookup_vector,result_vector) and isblank(value). We’ll start again from the inside out. Let’s look at ISBLANK. ISBLANK is a Boolean function (meaning that it returns either TRUE or FALSE) that looks at a cell and returns TRUE (or 1) if the cell is blank and FALSE (or 0) if the cell has something in it. But wait, we aren’t putting a ‘value’ in the argument, we’re putting a range! Exactly! Remember in the previous example? We used a range in COUNTIF and it turned it into an array constant (you know, the {} brackets)? That’s exactly what’s happening here! In fact, whenever an Excel formula is looking for a “value” or “criteria” and you give it a range instead of a single cell, it treats it as though it were an array constant! Pretty Cool, huh? Anyway, let’s get back to the explanation. So, ISBLANK is looking at an array of cells and returning a 1 or a 0 for each cell. Remember that 1 is the same as TRUE with excel and a 0 is the same as FALSE. What excel is seeing then is ISBLANK({1,1,1,1,0,0,0,0,etc.}). The ISBLANK formula is strange, it returns a 1 if the value is blank and a 0 if there is something in the cell. So our array basically inverts (flip flops) its values so you now have {0,0,0,0,1,1,1,1,etc.}. This means that the very first 1 is the first cell in your range that has no value in it.
OK, part 2. 1-({0,0,0,0,1,1,1,1,etc.}) inverts it again back to the original list so that all cells in the array with something in them are now 1 and all cells with nothing in them are now 0. Finally, we take that array and make it the denominator of a function so you have 1/{1,1,1,1,0,0,0,0,etc.}. When excel does this, it continues with it’s array and you get the following: {1,1,1,1,#DIV/0! ,#DIV/0! ,#DIV/0! ,#DIV/0!,etc.} because you can’t divide by 0, right? Well, let’s look at the lookup function as it exists now:
=LOOKUP(2, {1,1,1,1,#DIV/0! ,#DIV/0! ,#DIV/0! ,#DIV/0!,etc.}, result_vector)
This is saying, in the array that is the middle parameter (the one with the 1’s and the #DIV/0!’s), find the number 2 that results from the same range. You will NEVER find the number two because of something called a RBN or Really Big Number. Basically, compared to 1, 2 is a huge number and will never be reached. Why is this important? Because of 2 reasons: 1) the LOOKUP function itself doesn’t use exact matches, it uses approximate matches; therefore, if it doesn’t find a 2, it’ll look down the results list and find the next smallest value, which in this case is a 1. And 2) LOOKUP ignores errors and discounts them. Therefore anything resulting in an error (the DIV error) will disappear. The last value, the result_vector is important because that is the results list as to determine the value of the final cell. Essentially, we have the following once it’s all said and done:
= LOOKUP(2,{1,1,1,1},{23,564,554,9,<blank>,<blank>,<blank>,etc})
Which will return 9. : )
__________
If you don't know Richard Walter, you're missing out. In addition to being a wiz when it comes to Excel, this guy knows all the best bbq in Texas and will be happy to take you to get some! Find out more about Richard and SchoolStatus here:
