Election in USA in excel

[laim]

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-ignoreadding; 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>

 

[cyrilbrd]

Hi there,

Aren't the initials in "Name" States?
Would you like to regroup some states?

 

[laim]

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?

 

[cyrilbrd]

you could:
create a pivot table that would summarize your data:

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.

 

[kevinb11]

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
	A	B	C	D
1	Name	Vote	Vote	Vote
2	CA	55		55
3	TX	38		38
4	FL	29		29
5	NY	29		29
6	IL	20		20
7	PA	20		20
8	OH	18		18
9	MI	16		16
10	GA	16		16
11	NC	15		15
12	NJ	14	14	14
13	VA	13	13
14	WA	12	12
15	AZ	11	11
16	TN	11	11
17	IN	11	11
18	MA	11	11
19	MN	10	10
20	MO	10	10
21	WI	10	10
22	MD	10	10
23	CO	9	9
24	AL	9	9
25	SC	9	9
26	LA	8	8
27	KY	8	8
28	OR	7	7
29	OK	7	7
30	CT	7	7
31	NV	6	6
32	UT	6	6
33	KS	6	6
34	IA	6	6
35	AR	6	6
36	MS	6	6
37	NM	5	5
38	NE	5	5
39	WY	5	5
40	ID	4	4
41	RI	4	4
42	NH	4	4
43	ME	4	4
44	HI	4	4
45	MT	3	3
46	WY	3	3
47	ND	3	3
48	SD	3	3
49	DC	3	3
50	DE	3	3
51	VT	3	3
52	AK	3	3
53		538	282	270
Sheet3