Election in USA in excel

laim

New Member
Joined
May 2, 2012
Messages
2
Hello,

I'm new, so if I will make any errors, just pleas forgive me.

I would like to ask you for help. Today on my Government class in college we had to find data based on US map with electoral votes. I decided that I would like to do this in Excel.

In attachment you can find my sheet. If you will have few minutes maybe you could give me some tips how to find answer to this what I would like to find.

I would like to find:
a) The states with highest number of votes which collected 270 votes
b) Formula to find which states with lowest amount of votes when added could equal 270 votes. As I figured out there might be few variations to final list of states.




<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} .xl64 {border-top:1.0pt solid windowtext; border-right:none; border-bottom:none; border-left:1.0pt solid windowtext; background:yellow; mso-pattern:black none;} .xl65 {border-top:1.0pt solid windowtext; border-right:1.0pt solid windowtext; border-bottom:none; border-left:none; background:yellow; mso-pattern:black none;} --> </style> <table style="border-collapse: collapse;width:68pt" border="0" cellpadding="0" cellspacing="0" width="68"> <colgroup><col style="mso-width-source:userset;mso-width-alt:1578;width:37pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:1322;width:31pt" width="31"> </colgroup><tbody><tr style="height:16.0pt" height="16"> <td class="xl64" style="height:16.0pt;width:37pt" height="16" width="37">Name</td> <td class="xl65" style="width:31pt" width="31">Vote</td> </tr> <tr style="height:16.0pt" height="16"> <td class="xl63" style="height:16.0pt" height="16">WA</td> <td class="xl63" style="border-left:none" align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">OR</td> <td class="xl63" style="border-top:none;border-left:none" align="right">7</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">CA</td> <td class="xl63" style="border-top:none;border-left:none" align="right">55</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">NV</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">AZ</td> <td class="xl63" style="border-top:none;border-left:none" align="right">11</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">ID</td> <td class="xl63" style="border-top:none;border-left:none" align="right">4</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">MT</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">WY</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">UT</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">CO</td> <td class="xl63" style="border-top:none;border-left:none" align="right">9</td> </tr> <tr style="height:16.0pt" height="16"> <td class="xl63" style="height:16.0pt;border-top:none" height="16">NM</td> <td class="xl63" style="border-top:none;border-left:none" align="right">5</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">ND</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">SD</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">NE</td> <td class="xl63" style="border-top:none;border-left:none" align="right">5</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">KS</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">OK</td> <td class="xl63" style="border-top:none;border-left:none" align="right">7</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">TX</td> <td class="xl63" style="border-top:none;border-left:none" align="right">38</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">MN</td> <td class="xl63" style="border-top:none;border-left:none" align="right">10</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">IA</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">MO</td> <td class="xl63" style="border-top:none;border-left:none" align="right">10</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">AR</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">LA</td> <td class="xl63" style="border-top:none;border-left:none" align="right">8</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">WI</td> <td class="xl63" style="border-top:none;border-left:none" align="right">10</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">IL</td> <td class="xl63" style="border-top:none;border-left:none" align="right">20</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">KY</td> <td class="xl63" style="border-top:none;border-left:none" align="right">8</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">TN</td> <td class="xl63" style="border-top:none;border-left:none" align="right">11</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">MS</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">AL</td> <td class="xl63" style="border-top:none;border-left:none" align="right">9</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">IN</td> <td class="xl63" style="border-top:none;border-left:none" align="right">11</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">MI</td> <td class="xl63" style="border-top:none;border-left:none" align="right">16</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">OH</td> <td class="xl63" style="border-top:none;border-left:none" align="right">18</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">WY</td> <td class="xl63" style="border-top:none;border-left:none" align="right">5</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">VA</td> <td class="xl63" style="border-top:none;border-left:none" align="right">13</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">NC</td> <td class="xl63" style="border-top:none;border-left:none" align="right">15</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">GA</td> <td class="xl63" style="border-top:none;border-left:none" align="right">16</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">SC</td> <td class="xl63" style="border-top:none;border-left:none" align="right">9</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">FL</td> <td class="xl63" style="border-top:none;border-left:none" align="right">29</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">DC</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">MD</td> <td class="xl63" style="border-top:none;border-left:none" align="right">10</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">PA</td> <td class="xl63" style="border-top:none;border-left:none" align="right">20</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">DE</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">NJ</td> <td class="xl63" style="border-top:none;border-left:none" align="right">14</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">CT</td> <td class="xl63" style="border-top:none;border-left:none" align="right">7</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">RI</td> <td class="xl63" style="border-top:none;border-left:none" align="right">4</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">MA</td> <td class="xl63" style="border-top:none;border-left:none" align="right">11</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">NY</td> <td class="xl63" style="border-top:none;border-left:none" align="right">29</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">VT</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">NH</td> <td class="xl63" style="border-top:none;border-left:none" align="right">4</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">ME</td> <td class="xl63" style="border-top:none;border-left:none" align="right">4</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">HI</td> <td class="xl63" style="border-top:none;border-left:none" align="right">4</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">AK</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> </tr> </tbody></table>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi there,

Aren't the initials in "Name" States?
Would you like to regroup some states?
 
Upvote 0
I wrote down in 1st column name of state in 2nd how many votes does the state has.

I would like to find function which would allow me to list:
a) the biggest state who will collect 270 votes
b) the smallest states (state starts from the smallest number of votes) which will collect 270 votes.
c) of course in both cases I would like to have names of states and number of there votes, and total of votes - 270
d) last function which I'm looking for is: how many variations is for small states to collect 270 votes

do you have any idea?


Hi there,

Aren't the initials in "Name" States?
Would you like to regroup some states?
 
Upvote 0
you could:
create a pivot table that would summarize your data:
HTML:
Sum of Vote	
Row Labels	Total
AK	3
AL	9
AR	6
AZ	11
CA	55
CO	9
CT	7
DC	3
DE	3
FL	29
GA	16
HI	4
IA	6
ID	4
IL	20
IN	11
KS	6
KY	8
LA	8
MA	11
MD	10
ME	4
MI	16
MN	10
MO	10
MS	6
MT	3
NC	15
ND	3
NE	5
NH	4
NJ	14
NM	5
NV	6
NY	29
OH	18
OK	7
OR	7
PA	20
RI	4
SC	9
SD	3
TN	11
TX	38
UT	6
VA	13
VT	3
WA	12
WI	10
WY	8
Grand Total	538

Or you could also re-organise your data as follows:
Column B is Vote
ColumnC is Name

In E8 type:
=MAX(B2:B52)

in E17 type:
=MIN(B2:B52)

In F8 type:
=IFERROR(INDEX($C$2:$C$52,SMALL(IF($E$8=$B$2:$B$52,ROW($B$2:$B$52)-MIN(ROW($B$2:$B$52))+1,""),ROW(A1))),"")

Ctrl + Shift + Enter, not just enter, copy down till F14.

In F17 type:
=IFERROR(INDEX($C$2:$C$52,SMALL(IF($E$17=$B$2:$B$52,ROW($B$2:$B$52)-MIN(ROW($B$2:$B$52))+1,""),ROW(A1))),"")

Ctrl + Shift + Enter, not just enter, copy down till F30.
 
Upvote 0
Here are the first 2 arguments you wanted. Largest States to total 270 and smallest to total 270. Not sure what you want on the last argument. Notice that they both end at NJ. If you mean all the various ways to add up to 270, that would be too large to fit into a spreadsheet.

For instance: There are 635,013,559,600 different ways to pull 13 cards from a deck of playing cards which number 52.

Excel Workbook
ABCD
1NameVoteVoteVote
2CA5555
3TX3838
4FL2929
5NY2929
6IL2020
7PA2020
8OH1818
9MI1616
10GA1616
11NC1515
12NJ141414
13VA1313
14WA1212
15AZ1111
16TN1111
17IN1111
18MA1111
19MN1010
20MO1010
21WI1010
22MD1010
23CO99
24AL99
25SC99
26LA88
27KY88
28OR77
29OK77
30CT77
31NV66
32UT66
33KS66
34IA66
35AR66
36MS66
37NM55
38NE55
39WY55
40ID44
41RI44
42NH44
43ME44
44HI44
45MT33
46WY33
47ND33
48SD33
49DC33
50DE33
51VT33
52AK33
53538282270
Sheet3
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top