Today, Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen look at working with the Electoral College. Using the State by State Electoral Vote Count, Mike and Bill will sort out which States had the highest number of votes collecting the required 270 votes and which States had the lowest number of votes which added up to 270 votes. Learn a little about the Electoral College, why the numbers matter, see why simple Filters will not do the complete job, why Bill chooses to use a Pivot Table and Mike goes with Functions such as =LARGE, =INDEX, =SMALL and more. A real Information-Packed Dueling Podcast! Check out Episode #1571.
Dueling Excel Podcast #109...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Dueling Excel Podcast #109...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle] Slaying Excel Dragons
and
"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
Dueling Excel podcast episode 109, Electoral College.
Bill Jelen: I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fine.
All right, great question today Electoral College, this is first government class, want to figure out the states with the highest number of votes which will add up to 270.
That's the magic number to elect a president and the states with the lowest amount of votes which will equal to 70. My first thought filter and then use top 10 but top 10 doesn't have what I need unfortunately. It's the old top 10 which gives us items and percent so I'm canning that idea going out for the Pivot Table. So let's get rid of that common hair show, comments off, insert Pivot Table. We're going to put it right here on the current worksheet, BAM there, click OK. All right, name going down the left-hand side and vote in the heart of the Pivot Table will move the field list. It just gets us the exact same list, well, that wasn't anything Bob but check this out. Couple things first of all more sort options. I'm going to sort descending based on Sum of the Vote that gets the largest states to the top and then here's the one I really was looking for. Value filters just outside of the screen, it says top 10 dot dot. Starting in Excel2007 they improve this dramatically with items and percent which we've always had but also the cool one called Sum. So in Sum don't use the spin button by the way, just type in there. We were looking for enough records to get us 270 as the Sum and I'll click Ok. So here we go, here are the states that add up to 270 California, Texas, New York, isn't that cool? And if we go the other way, so back 10, back down to the top 10 filter and say that I want the bottom States that's the smallest ace how to get me 270. Now this here I'm going to sort the other way, just because I I think it would look better, more sort options a setting, click ok. Alright, so Alaska, Vermont, South Dakota, Montana, all three Electoral College as I cruise down here though, you see that we end up with 282. Why did we get 282 when I asked for 270, okay, because I asked for 270 if they had given me through Virginia and not New Jersey, I would have ended up with 268 and that's not 270. They need to get me at least 270, so New Jersey is including list to get us to 82. Isn't it funny that New Jersey is on the margin, is the the last state in either list. All right Mike, let's see what you have.
Mike Girvin: Thanks MrExcel. Oh man, that was so amazing. The filter in the Pivot Table value filters top 10 Sum. I had no idea that was there. That's the point.
All right, so we'll see you next tool you know doing a pivot table like this that is the way the formulas that you got to use to do something like this are ridiculous right?
So Pivot Tables ninety-nine percent of time the only time you use formulas if these numbers are changing all the time and you want something super dynamic. All right, so I'm going to have to do this in a few steps. I'm first going to use the large function to extract the first largest, second largest etc., ctrl shift down our F4 comma 2 cells to my left that'll extract the first and then the second, third largest etc. So I copy that down and then I need a cumulative total ALT Equals.
I'm going to shift colon and then lock the first one with the F4 key, that will give me an expandable range that will add cumulatively as I go down, that range is expanding. So it's right here, that's the cutoff.
Now here is the problem we could do if there were no duplicates here, which is crazy assumption.
We just do a straight look up but there's, there's two 29s, two20s, two 16s. So you have to switch over to a formula that can handle duplicates as the lookup value. I'm going to use =INDEX( and it's a big array formula.
Well, what are we looking up, that's the array right there, ctrl shift down LF4 comma, and again the trouble comes for row number right?
For the first 155 which is right there we need 1 2 3 and that will give us California back to the cell but down here we have duplicates.
So we have multiple row numbers and we're going to have to use the small function to extract the correct row number as we copy our formula down. So SMALL(. I need row numbers,that's what I need to dump into this row number argument.
So I'm going to say IF( anything in this range right here ctrl shift down our F4 is equal to one cell to my left. Now for the 55 they'll only be one, for the 29s they'll be two right, comma. Well, what I want that's the condition what I want is a row number. So I'm going to say row ctrl shift down our F4. That will give me all the rows 789 etc, so I need to subtract from that row F4 that gives me 7-7 which is 0 so I don't want that as the first one so I plus 1. Alright, so I'm going to close parenthesis on the IF that you don't need the false the K.
Now here's another tricky thing. Sometimes when you have duplicate lookup values you put rose function in the case. I'm going to comma, it gives us the number one two three four as we copy down. But we really don't want that. We want one too and then one two and then one. So we're going to do COUNTIF( with an expandable range, one cell to my left I did the right arrow, column, on my right arrow close parenthesis and we'll lock. We're using the F4 key that right there. So now that will give us a count as we go down the correct K for the ROW number 2 extract, close parentheses on the small. This IF right here logical test its expecting a single true or false because we're giving it a bunch. It will require control shift enter. Alright, so there's our ROW number, that's the small.
Close parenthesis on index and we're going to have to control shift and enter, not just enter. And then I'm going to double click and send it down and so there we have it, we get down to 270 right there, 270 that's our cut off. So we could at this point just take this and print it right? But if you wanted to turn the rest of this on so it was dynamic we're going to have to somehow pick out this ROW.
Now if you're in 2010 you can substitute that SMALL with AGGREGATE and then you don't have to use control shift enter. All right now let's figure out how to find this row because the problem is if this turns to 56 we can't exactly search for 270. I'm going to ctrl Z and keep that 55 while we can find the position of that 270 with a =MATCH. I'm going to say =MATCH(, look up to 70 in this range right here. I'm going to ctrl backspace because i don't need to lock that. Right now if I close parenthesis we can use approximate match which is the default and because it's 270 it's exactly correct. It's the 11th record so then we could use that fact in our formulas or conditional formatting but check this out.
If this is 56 then it gives us the wrong row number 10 because that match can't find a 270 so we need to add 1 when 270 is not in the list. So I'm going to say + MATCH(. I'm actually do the same little piece here again but if now if we do an exact match, right, approximate match will find a position when 270 is not in the list. But if we do exact match this then because 270 is not in the list, F9 is the pause. So I have to go up here to formulas and calculate. It gives me an NA control Z. Well, I can convert that to a true by doing +ISNA and then +ISNA(MATCH exact will give us a true but when we add using the plus it will give us a 1. So it'll always add 11that can't find 270 in the list.
All right, now that number right there, you could come here and amend each one of these formulas using an IF but check this out. I'm just going to use conditional formatting Alt OD, new rule, formula and right here i'm going to say equals, oops I didn't need to put equals.
I could just click right here E7 but check this out. I'm going to hit the F4 key and lock column reference but not the row when that is greater than 11 locked in all directions.
Then, when it's greater, than that means true, I can turn off something in the formatting so watch this I'm going to hit format. I'm going to say in two thousand, earlier versions you might have to do a font color white but Number Tab. I can go down to Custom and I'm just going to turn it all off. Semicolon semicolon semicolon, three semicolons means show nothing, text own numbers. Click OK, click OK, click OK. And so now I should have my setup working when I change this to 55, it's all working fine. Now the only thing you have to change for the SMALL version over here is simply use SMALL there instead of LARGE. All the other formulas are the same.
All right, go back to MrExcel.
Bill Jelen: Well, hey, we'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
Bill Jelen: I'm Bill Jelen from MrExcel, will be joined by Mike Girvin from Excel Is Fine.
All right, great question today Electoral College, this is first government class, want to figure out the states with the highest number of votes which will add up to 270.
That's the magic number to elect a president and the states with the lowest amount of votes which will equal to 70. My first thought filter and then use top 10 but top 10 doesn't have what I need unfortunately. It's the old top 10 which gives us items and percent so I'm canning that idea going out for the Pivot Table. So let's get rid of that common hair show, comments off, insert Pivot Table. We're going to put it right here on the current worksheet, BAM there, click OK. All right, name going down the left-hand side and vote in the heart of the Pivot Table will move the field list. It just gets us the exact same list, well, that wasn't anything Bob but check this out. Couple things first of all more sort options. I'm going to sort descending based on Sum of the Vote that gets the largest states to the top and then here's the one I really was looking for. Value filters just outside of the screen, it says top 10 dot dot. Starting in Excel2007 they improve this dramatically with items and percent which we've always had but also the cool one called Sum. So in Sum don't use the spin button by the way, just type in there. We were looking for enough records to get us 270 as the Sum and I'll click Ok. So here we go, here are the states that add up to 270 California, Texas, New York, isn't that cool? And if we go the other way, so back 10, back down to the top 10 filter and say that I want the bottom States that's the smallest ace how to get me 270. Now this here I'm going to sort the other way, just because I I think it would look better, more sort options a setting, click ok. Alright, so Alaska, Vermont, South Dakota, Montana, all three Electoral College as I cruise down here though, you see that we end up with 282. Why did we get 282 when I asked for 270, okay, because I asked for 270 if they had given me through Virginia and not New Jersey, I would have ended up with 268 and that's not 270. They need to get me at least 270, so New Jersey is including list to get us to 82. Isn't it funny that New Jersey is on the margin, is the the last state in either list. All right Mike, let's see what you have.
Mike Girvin: Thanks MrExcel. Oh man, that was so amazing. The filter in the Pivot Table value filters top 10 Sum. I had no idea that was there. That's the point.
All right, so we'll see you next tool you know doing a pivot table like this that is the way the formulas that you got to use to do something like this are ridiculous right?
So Pivot Tables ninety-nine percent of time the only time you use formulas if these numbers are changing all the time and you want something super dynamic. All right, so I'm going to have to do this in a few steps. I'm first going to use the large function to extract the first largest, second largest etc., ctrl shift down our F4 comma 2 cells to my left that'll extract the first and then the second, third largest etc. So I copy that down and then I need a cumulative total ALT Equals.
I'm going to shift colon and then lock the first one with the F4 key, that will give me an expandable range that will add cumulatively as I go down, that range is expanding. So it's right here, that's the cutoff.
Now here is the problem we could do if there were no duplicates here, which is crazy assumption.
We just do a straight look up but there's, there's two 29s, two20s, two 16s. So you have to switch over to a formula that can handle duplicates as the lookup value. I'm going to use =INDEX( and it's a big array formula.
Well, what are we looking up, that's the array right there, ctrl shift down LF4 comma, and again the trouble comes for row number right?
For the first 155 which is right there we need 1 2 3 and that will give us California back to the cell but down here we have duplicates.
So we have multiple row numbers and we're going to have to use the small function to extract the correct row number as we copy our formula down. So SMALL(. I need row numbers,that's what I need to dump into this row number argument.
So I'm going to say IF( anything in this range right here ctrl shift down our F4 is equal to one cell to my left. Now for the 55 they'll only be one, for the 29s they'll be two right, comma. Well, what I want that's the condition what I want is a row number. So I'm going to say row ctrl shift down our F4. That will give me all the rows 789 etc, so I need to subtract from that row F4 that gives me 7-7 which is 0 so I don't want that as the first one so I plus 1. Alright, so I'm going to close parenthesis on the IF that you don't need the false the K.
Now here's another tricky thing. Sometimes when you have duplicate lookup values you put rose function in the case. I'm going to comma, it gives us the number one two three four as we copy down. But we really don't want that. We want one too and then one two and then one. So we're going to do COUNTIF( with an expandable range, one cell to my left I did the right arrow, column, on my right arrow close parenthesis and we'll lock. We're using the F4 key that right there. So now that will give us a count as we go down the correct K for the ROW number 2 extract, close parentheses on the small. This IF right here logical test its expecting a single true or false because we're giving it a bunch. It will require control shift enter. Alright, so there's our ROW number, that's the small.
Close parenthesis on index and we're going to have to control shift and enter, not just enter. And then I'm going to double click and send it down and so there we have it, we get down to 270 right there, 270 that's our cut off. So we could at this point just take this and print it right? But if you wanted to turn the rest of this on so it was dynamic we're going to have to somehow pick out this ROW.
Now if you're in 2010 you can substitute that SMALL with AGGREGATE and then you don't have to use control shift enter. All right now let's figure out how to find this row because the problem is if this turns to 56 we can't exactly search for 270. I'm going to ctrl Z and keep that 55 while we can find the position of that 270 with a =MATCH. I'm going to say =MATCH(, look up to 70 in this range right here. I'm going to ctrl backspace because i don't need to lock that. Right now if I close parenthesis we can use approximate match which is the default and because it's 270 it's exactly correct. It's the 11th record so then we could use that fact in our formulas or conditional formatting but check this out.
If this is 56 then it gives us the wrong row number 10 because that match can't find a 270 so we need to add 1 when 270 is not in the list. So I'm going to say + MATCH(. I'm actually do the same little piece here again but if now if we do an exact match, right, approximate match will find a position when 270 is not in the list. But if we do exact match this then because 270 is not in the list, F9 is the pause. So I have to go up here to formulas and calculate. It gives me an NA control Z. Well, I can convert that to a true by doing +ISNA and then +ISNA(MATCH exact will give us a true but when we add using the plus it will give us a 1. So it'll always add 11that can't find 270 in the list.
All right, now that number right there, you could come here and amend each one of these formulas using an IF but check this out. I'm just going to use conditional formatting Alt OD, new rule, formula and right here i'm going to say equals, oops I didn't need to put equals.
I could just click right here E7 but check this out. I'm going to hit the F4 key and lock column reference but not the row when that is greater than 11 locked in all directions.
Then, when it's greater, than that means true, I can turn off something in the formatting so watch this I'm going to hit format. I'm going to say in two thousand, earlier versions you might have to do a font color white but Number Tab. I can go down to Custom and I'm just going to turn it all off. Semicolon semicolon semicolon, three semicolons means show nothing, text own numbers. Click OK, click OK, click OK. And so now I should have my setup working when I change this to 55, it's all working fine. Now the only thing you have to change for the SMALL version over here is simply use SMALL there instead of LARGE. All the other formulas are the same.
All right, go back to MrExcel.
Bill Jelen: Well, hey, we'll see you next week for another dueling Excel podcast from MrExcel and Excel Is Fun.
