Excel Challenge - Fewest States to Reach 270 Electoral College Votes - Excel - Episode 1571

2,169 views · Published 19 July 2012 · 9:59 · Indexed 30 October 2026

Channel: MrExcel.com · 2012 · Education

Watch on YouTube

Microsoft Excel Tutorial: Reaching 270 Electoral College Votes with Fewest States.

Welcome to another episode of Dueling Excel, where we take on challenging Excel problems and find the best solutions. In this episode, we tackle the topic of the Electoral College, a crucial aspect of the American political system. Joining me, Bill Jelen from MrExcel, is Mike Girvin from Excel Is Fun. Together, we will dive into this complex issue and use Excel to find the states with the highest and lowest number of votes, which add up to the magic number of 270 needed to elect a president.

At first, I thought we could simply use the filter and top 10 function, but unfortunately, it didn't give us the results we needed. So, we turned to the trusty Pivot Table. By inserting a Pivot Table and sorting it in descending order based on the sum of votes, we were able to get a list of the states with the highest number of votes. But that wasn't all, we also discovered the value filters option, which allowed us to specify the sum we were looking for, in this case, 270. This gave us the top 10 states that add up to 270, including California, Texas, and New York.

But what about the states with the lowest number of votes? Well, we simply reversed the process and used the bottom 10 filter to get the smallest states that add up to 70. However, we noticed that the total was 282 instead of 270. This is because the states needed to add up to at least 270, so Excel included New Jersey to reach that number. It's interesting to see that New Jersey is the last state on both lists, showing its significance in the Electoral College.

Now, let's see what Mike has in store for us. He introduces us to the large function, which extracts the first, second, and third largest values. By using this function and a cumulative total, we were able to get a list of states that add up to 270. However, we faced a challenge when dealing with duplicate values. Mike expertly uses the index and small functions to handle this issue and create a dynamic formula that can be copied down. He also shows us how to use conditional formatting to highlight the states that add up to 270 and make the solution even more dynamic.

In conclusion, we hope this episode has shed some light on the Electoral College and how Excel can be used to solve complex problems. Don't forget to tune in next week for another exciting episode of Dueling Excel, where we continue to explore the endless possibilities of this powerful tool. Thank you for watching and don't forget to like, share, and subscribe to our channel for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Using a Pivot Table to find states with highest and lowest number of votes.
(01:03) Using the Value Filters option to find the top 10 states based on vote count.
(02:26) Using the Large and Cumulative Total functions to find the states that add up to (06:23) (03:05) Using the Index and Small functions to handle duplicate values in the list. 
(04:50) Using the Match function to find the position of the cutoff point for 270 votes.
(08:40) Using conditional formatting to highlight states with more than 11 votes.
(09:40) Clicking Like really helps the algorithm

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] http://www.mrexcel.com/slayingdragonsebook2011.html 

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! http://www.mrexcel.com/learn2010/LE2010.html 

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153381/

More from this channel