Who knows Excel and shit?

[lisarea]

I want to do some crap for no reason, but I am having a dumb number problem, and I don't know how to use Excel, and I don't know how to do math, and I am JUST A GIRL, so someone has to help me. My dumb brother has not answered my email since, like, AN HOUR AGO, so he's no good for nothing. EVEN THOUGH I AM THE DOUBLE-BOSS OF HIM. STUPID REID.

If you help me, I'll give you a gmail account for me to send dumb questions to, plus be your new best friend, and maybe I will alter a photo of you to add a glistening tear and a halo or something like that.

I'm going to go start some spaghetti sauce, but I at least have to do this tomorrow.

[Farren]

I want to do some crap for no reason, but I am having a dumb number problem, and I don't know how to use Excel, and I don't know how to do math, and I am JUST A GIRL, so someone has to help me. My dumb brother has not answered my email since, like, AN HOUR AGO, so he's no good for nothing. EVEN THOUGH I AM THE DOUBLE-BOSS OF HIM. STUPID REID.

If you help me, I'll give you a gmail account for me to send dumb questions to, plus be your new best friend, and maybe I will alter a photo of you to add a glistening tear and a halo or something like that.

I'm going to go start some spaghetti sauce, but I at least have to do this tomorrow.

Excel is easy. Shit can be complicated, especially if its fractal. If your problem is primarily in the field of shit, count me out. However, if you want to know how to do anything at all in Excel, ask, and you shall be illuminated with the light of knowledge. Email or right here on this thread. Your choice.

[livius drusus]

That E looking thing adds shit up.

[lisarea]

Excel is easy. Shit can be complicated, especially if its fractal. If your problem is primarily in the field of shit, count me out. However, if you want to know how to do anything at all in Excel, ask, and you shall be illuminated with the light of knowledge. Email or right here on this thread. Your choice.

It is, kind of. About shit.

This is kind of like that boring electoral vote project I was working on before. I decided I want to add more stuff, but I have a wonky number in this one part I need for the other stuff.

So, I just took the US population and divided it by 535 (I haven't been using DC in this). This gave me the bloc size of voters that would likely gain an electoral vote IF the votes were doled out proportionally.

Then, I divided each state's population by that, to come up with how many electoral votes they deserve based solely on population. That row equaled 535.06.

Then, I divided the actual population by that number, to get the weight of the individual votes in each state.

THIS IS WHERE IT GOES ALL HOOEY: The average vote weight is 1.31. Shouldn't it be closer to 1? What am I doing wrong? I know this is something I should know, but I am too busy knowing stupid things to know that, I guess.

What I want to do now is just figure the average weights for the solid red, solid blue, and go either way states. Then, I have this other thing I want to do with calculating eligible voters vs. population, and maybe even get the average vote weights for different racial groups, just to be an asshole and all. Not like anyone is going to read it or anything. But being an asshole is important to me, whether anyone knows about it or not.

So, liv?

I didn't know that about the E looking thingie. I didn't even notice that before you said.

[Goliath]

Hmmm....I'm not completely sure I understand what you're trying to do, lisarea, but the discrepency of the extra .31 could come from the extra .06 when you calculated total electoral votes based on population alone.

Then again, I could be full of crap...like I said, I'm not sure I understand what you're doing.

[JoeP]

Nor me (understand completely what you're trying to do) but an alarm bell sounds: if you're taking averages of averages, you can get out-of-line results. Post the actual numbers, or upload the spreadsheet. (Do we do attachments here?)

[viscousmemories]

Nor me (understand completely what you're trying to do) but an alarm bell sounds: if you're taking averages of averages, you can get out-of-line results. Post the actual numbers, or upload the spreadsheet. (Do we do attachments here?)
Attachments, yes. Excel attachments, no. I'll do some research, but I don't think it's a good idea to allow Excel attachments because of the potential for malicious macros.

[livius drusus]

So, liv?

Erm... That a thing with the arrows merges cells and centers the entry.

[JoeP]

So, liv?

Erm... That a thing with the arrows merges cells and centers the entry.
I thought we were talking about the E thing not the a thing ... and I thought the E-like thing was the sigma for inserting a sum formula.

[livius drusus]

I thought we were talking about the E thing not the a thing ...

We were, but that wasn't enough for lisarea. She wrote this whole big thing in the midnight hour, crying more, more, more like she's fucking Billy Idol or something. The A thing was the best I could do.

and I thought the E-like thing was the sigma for inserting a sum formula.

Yeah, so? Smartypants.

[JoeP]

You allow zip files so in principle anything could be packed in there. I don't know if you should worry about disallowing excel files because of the macro issue. It will never infect the board and it will never infect anyone who doesn't open the attachment. But I suppose the same argument applies to emails...

Test zip of a random excel file attached.

[viscousmemories]

Hmm... very good point. Maybe we shouldn't allow .zip files. :P

[Chiron]

Yo lisarea, I don't know if this would be helpful to you in the maximal extreme, but I have a Universal Resource Locator of fun and happiness for you!
http://www.electoral-vote.com/info/states.html

Sure, it ain't Excel and shit, but it has lots o' data that might prove useful. And (from my sleepy-clouded memory from last night of what you're doing) it may even already have what you're trying to compute!

Y'know what'd be funny? A lossy compression/archiving format -- I mean, mp3 files are so popular already, so why not for regular files, too? Think of the benefits we could reap! You compress some important documents into a .bim file (.bim symbolizing things entirely unrelated to the intellect of you, the user of the .bim compression scheme) and choose how lossy you wish the compression to be, and then open them years later and find only a couple of hugely-mangled sentences in there! Ah, technology, will thine wonders ne'er cease!?

K

[Farren]

So, I just took the US population and divided it by 535 (I haven't been using DC in this). This gave me the bloc size of voters that would likely gain an electoral vote IF the votes were doled out proportionally.


OK This will give you Citizens per Electoral Vote. I get 547,715.09 from the CIA World factbook stated population of 293,027,571 people.

Its important you use a formula here rather than precomputing the bloc size and putting that literal number in because the ".09" at the end of my bloc size figure is actually some long-ass fraction which could produce a ballooning discrepancy as you use the number in other calculations.

So if the US population is, for instance in cell A2 of the spreadsheet then the formula in the bloc size cell would be:

A2/535


Then, I divided each state's population by that, to come up with how many electoral votes they deserve based solely on population. That row equaled 535.06.


So that should give you Electoral Votes per State. Once again its important that you use a formula rather than precalculating and that formula is based on the cell containing the calculated bloc size.

So if the formula for bloc size mentioned above is in cell B2 and the population of each state is found in cells C2 - C52, the formula for each states fair allocation of electoral votes should be (formula for first state shown - if copied to subseqent rows underneath it the 2 in "C2" will automatically be updated to the number of the corresponding row):

C2/B$2

Where the $ signs will ensure that when you copy the formula to subsequent rows (for each state), the row number of the second value will not change, so copying the formula will produce

C3/B$2
C4/B$2
...

for each state. Its important as I said earlier to use the actual computed value for bloc size in B2. Don't do something like this

C2/547,715.09
C3/547,715.09
C4/547,715.09
...

Because when you add these numbers up you'll get a discrepency because of the fractional part of the actual bloc size which is rounded up to however many decimal places you're showing. For instance when I format my bloc size calculation to more decimal places, I get 547,715.08591308.

If you use the actual calculated value and not some value that you actually typed in, adding up the resulting total votes for all states (which is where I presume you arrived at 535.06) should add up to exactly 535.


Then, I divided the actual population by that number, to get the weight of the individual votes in each state.


Which population are you dividing by which number? Its not clear from the sentence

Scenario 1:
Are you dividing the total poplation of the state or country by the sum of the electoral votes of all states mentioned above? If so that seems a little pointless because it would be the same as dividing by the total electoral votes.

Scenario 2:
Alternatively, if you're dividing the population of the state by the calculated proportional amount of electoral votes for each state, I can't see much point in that either, since the value you calculated for each state is already proportional to population and would consequently produce the same proportion of the vote for citizens of every state.

EG. If the calculated votes for S. Carolina are

4,147,152 / 547,715.09 = 7.57 Votes

Then the vote/person in S. Carolina will be

4,147,152 / 7.57 = 547,715.09

The vote/person of every state will be 547,715.09 because you're calculating the votes/state from their populations

I'm not following what you're trying to do here but if you can clarify perhaps I can help.

[lisarea]

Yo lisarea, I don't know if this would be helpful to you in the maximal extreme, but I have a Universal Resource Locator of fun and happiness for you!
http://www.electoral-vote.com/info/states.html

Sure, it ain't Excel and shit, but it has lots o' data that might prove useful. And (from my sleepy-clouded memory from last night of what you're doing) it may even already have what you're trying to compute!

Oh, don't even get me started.

I have to figure out how to fix my problem. I kind of know what it is now, but it takes me multiple hours to wake up thoroughly enough to explain stuff. And I only woke up a couple of hours ago, and I already done been dicking around with it a bunch. I just...I'm trying to explain it, but I'm thinking I should go take a shower or something because sometimes that makes me figure stuff out because taking a shower is boring. Plus, I probably smell bad and maybe the stink lines are distracting me.

But yeah. I have all this data from the census bureau and that site and all this other stuff already that I'm trying to incorporate. One of the things I was doing yesterday was figuring the average weight of votes in red vs. blue vs. either way states. I also have some other stuff like that, too, like figuring adult vs. child ratios to figure out closer to the actual value of eligible voters and such. I just need to fix some of these numbers in the middle so the rest is right. And what's bugging me is I keep seeing these statistics about things like this, and I'm all like, "Oh, COOL!" and then I look at it for a minute and: WRONG. Everything is all wrong.


Y'know what'd be funny? A lossy compression/archiving format -- I mean, mp3 files are so popular already, so why not for regular files, too? Think of the benefits we could reap! You compress some important documents into a .bim file (.bim symbolizing things entirely unrelated to the intellect of you, the user of the .bim compression scheme) and choose how lossy you wish the compression to be, and then open them years later and find only a couple of hugely-mangled sentences in there! Ah, technology, will thine wonders ne'er cease!?

Thank you for making me look less insane by comparison!

I needed that! For my self-esteem and all.

[lisarea]

OK This will give you Citizens per Electoral Vote. I get 547,715.09 from the CIA World factbook stated population of 293,027,571 people.

That's probably more correct now, but the electoral votes (and representation in congress) are figured according to the census from years ending in zero.

The data they used is here. (http://www.census.gov/population/www/censusdata/apportionment.html) I downloaded the spreadsheet and started from there.


Its important you use a formula here rather than precomputing the bloc size and putting that literal number in because the ".09" at the end of my bloc size figure is actually some long-ass fraction which could produce a ballooning discrepancy as you use the number in other calculations.

Yeah, I've been doing it with the formulas. I've changed the formulas and numbers enough that if I didn't have the totals calculated dynamically, I'd be even more screwed up than I am.


So that should give you Electoral Votes per State. Once again its important that you use a formula rather than precalculating and that formula is based on the cell containing the calculated bloc size.

So if the formula for bloc size mentioned above is in cell B2 and the population of each state is found in cells C2 - C52, the formula for each states fair allocation of electoral votes should be (formula for first state shown - if copied to subseqent rows underneath it the 2 in "C2" will automatically be updated to the number of the corresponding row):

C2/B$2

Where the $ signs will ensure that when you copy the formula to subsequent rows (for each state), the row number of the second value will not change, so copying the formula will produce

C3/B$2
C4/B$2
...

PHBBBBTTTT!

I don't even want to talk about how much time I spent typing that crap in by hand before I figured out the dollar sign thing. I blame you, Farren. I think that you should have known that I was doing something stupid and intervened much sooner.

I even asked the Little Muffin how to do that, and he just taunted me for being stupid, so I told him to do it, but Mr. Smartypants couldn't remember how either, so I made him sit there and type the formulas in by hand. Which is when he changed the Population field to Butt sex.

But I digress.


Because when you add these numbers up you'll get a discrepency because of the fractional part of the actual bloc size which is rounded up to however many decimal places you're showing. For instance when I format my bloc size calculation to more decimal places, I get 547,715.08591308.

WAITAMINNIT! WHAT? It CALCULATES the numbers based on the numbers AS THEY'RE SHOWN? Holy K-RAP! I was just assuming it'd calculate them based on the actual numbers, and just round them for display purposes.

I need to go screw with this some. It never even occurred to me it'd do it like that! I'm serious!

This is at least part of my problem, then.

Man, that's crazy, though. Just crazy. Why would anyone want to do that? I was just shortening the numbers to 2 decimal places to make it easier for me to read. Cripes.


I'm not following what you're trying to do here but if you can clarify perhaps I can help.

Yeah, I'm being very inarticulate here. I'm going to post a more thorough explanation of what I'm trying to do, with illustrations of what I'm talking about, which should address some of the rest. As I said, I think I've kind of started to isolate the problem, but I kind of have to show it, I guess, because I don't know the big words and stuff.

[Farren]

Because when you add these numbers up you'll get a discrepency because of the fractional part of the actual bloc size which is rounded up to however many decimal places you're showing. For instance when I format my bloc size calculation to more decimal places, I get 547,715.08591308.

WAITAMINNIT! WHAT? It CALCULATES the numbers based on the numbers AS THEY'RE SHOWN? Holy K-RAP! I was just assuming it'd calculate them based on the actual numbers, and just round them for display purposes.

I need to go screw with this some. It never even occurred to me it'd do it like that! I'm serious!

This is at least part of my problem, then.

Man, that's crazy, though. Just crazy. Why would anyone want to do that? I was just shortening the numbers to 2 decimal places to make it easier for me to read. Cripes.

Nonononono! Wait. I mean't its important not to look at the number as its shown and type it as a literal value in a formula elsewhere (as in, you see 500.07 so you enter a formula elsewhere as X5/500.07).

It doesn't use the display value as the value for calculation if you're referring to the actual calculation (e.g. X5/C5) it uses the actual value. You initial assumption was correct. I obviously didn't express myself properly. I was just cautioning you against looking at a number then typing it in literally elsewhere on your worksheet.

[lisarea]

Nonononono! Wait. I mean't its important not to look at the number as its shown and type it as a literal value in a formula elsewhere (as in, you see 500.07 so you enter a formula elsewhere as X5/500.07).

It doesn't use the display value as the value for calculation if you're referring to the actual calculation (e.g. X5/C5) it uses the actual value. You initial assumption was correct. I obviously didn't express myself properly. I was just cautioning you against looking at a number then typing it in literally elsewhere on your worksheet.

Oh. OK.

Yeah, I haven't been using regular typed-in numbers except for the stuff that is literal typed-in numbers (actual populations and actual electoral votes).

Thank you for being all handsome and all.

So.

OK. I'm muddling it up because I messed with it for a long time and had all kinds of other stuff in there. This is what's relevant, I think:

State Pop. EVs BPE(1) Prop.(2) Relative Value(3)



Wyoming 495,304 3 165101.33 0.94 3.19
Vermont 609,890 3 203296.67 1.16 2.59
Alaska 628,933 3 209644.33 1.20 2.51
North Dakota 643,756 3 214585.33 1.22 2.45
South Dakota 756,874 3 252291.33 1.44 2.08
Delaware 785,068 3 261689.33 1.49 2.01
Montana 905,316 3 301772.00 1.72 1.74

Rhode Island 1,049,662 4 262415.50 2.00 2.00
Hawaii 1,216,642 4 304160.50 2.31 1.73
New Hampshire 1,238,415 4 309603.75 2.35 1.70
Maine 1,277,731 4 319432.75 2.43 1.65
Idaho 1,297,274 4 324318.50 2.47 1.62

Nebraska 1,715,369 5 343073.80 3.26 1.53
West Virginia 1,813,077 5 362615.40 3.45 1.45
New Mexico 1,823,821 5 364764.20 3.47 1.44
Nevada 2,002,032 5 400406.40 3.81 1.31
Utah 2,236,714 5 447342.80 4.25 1.18

Arkansas 2,679,733 6 446622.17 5.09 1.18
<...>So:

(1) State population / EVs = Bloc per Elector
Total US population / Total EVs = National Bloc Size (not shown)
(I also have the average bloc size, but I'm not using it for this)
(2) State population / National bloc size = Proportional vote *
(3) EV / Proportional vote = Relative value[/FONT]
This is where the problem starts, with the extra .06 added to the 535 (DC is not in here). THEN, the numbers go even wonkier at the next step, when the electoral votes are divided by the proportional votes to get their relative value. I sort of know what the problem is here, but I'm not sure how to describe or fix it because I am stupid.

So, I will try to explain what I'm thinking, because I sort of know where the problems are, anyway. Please note that I am being very brave here, because I KNOW I'm doing something stupid and now the whole world is going to find out just how stupid I am:

There are numbers for the relative value of electoral votes already, based on the bloc size that each vote in each state represents. So, each EV in Wyoming represents 165101.33 people (a population of 495,304 represented by 3 electors), and each EV in California represents 616923.60 (population of 33,930,798 represented by 55 electors). The thing I don't like about the ones I've seen is:

A) They usually have the populations wrong. I think they're using the most recent year stats, rather than the 2000 ones, which usually makes it look scarier but is either an honest mistake or outright disingenuous.

B) They base the whole thing on the assumption that Wyoming is the baseline. That is, ALL electoral votes are apportioned based on Wyoming, making the national bloc size 165101.33. That's not right. Those three votes are defaults, not apportioned. So the apportioned numbers are the averages of all states having more than three electors, and you work back from there, so Wyoming has fewer than one vote, instead of three. Does that make sense? See, the things I've seen come out as though each Wyoming resident gets one full vote, and the other states' residents get some fraction of that. I don't think that's so right.

So, I can do one of two things:

I can figure the average bloc size it took to trigger another EV in 2000. I can do that in a couple-few different ways. I can divide the US population by the number of electoral votes, or I can divide the US population minus the population of the three vote states by the number of electoral votes minus the three vote states' EVs. The former is easier, but the latter would be more accurate, really, because the former assumes that the default vote states are proportional, which they aren't.

So: What I need, I guess is a way to take the latter number (538131.42) and apply that to the whole, including the three-vote states, and including all electoral votes, so that the final figures are still based on the real population and the 535 EVs.

Where I'm getting stuck is, how do I establish the baseline? I based the relative weight by comparing the existing electoral votes and what they would be if they were strictly proportional.

So, how do I discount those seven three-vote states, and then go back and account for the difference between actual and average sizes?

Know what's bugging me? We have almost no spoons in this house. We keep running out of spoons way before the dishwasher is full. So I think I'm just going to go to the store and buy some spoons. Probably when I come back with a bag o' new spoons, I'll be able to figure this out.

[lisarea]

FARREN!

Why didn't you tell me you can't just buy regular spoons separately?

I don't want more forks and butter knives. Butter knives. How fucking lame. I'm not paying for more stupid butter knives!

[Farren]

Thank you for being all handsome and all.


Its a congenital defect.


So.

OK. I'm muddling it up because I messed with it for a long time and had all kinds of other stuff in there. This is what's relevant, I think:

State Pop. EVs BPE(1) Prop.(2) Relative Value(3)



Wyoming 495,304 3 165101.33 0.94 3.19
Vermont 609,890 3 203296.67 1.16 2.59
Alaska 628,933 3 209644.33 1.20 2.51
North Dakota 643,756 3 214585.33 1.22 2.45
South Dakota 756,874 3 252291.33 1.44 2.08
Delaware 785,068 3 261689.33 1.49 2.01
Montana 905,316 3 301772.00 1.72 1.74

Rhode Island 1,049,662 4 262415.50 2.00 2.00
Hawaii 1,216,642 4 304160.50 2.31 1.73
New Hampshire 1,238,415 4 309603.75 2.35 1.70
Maine 1,277,731 4 319432.75 2.43 1.65
Idaho 1,297,274 4 324318.50 2.47 1.62

Nebraska 1,715,369 5 343073.80 3.26 1.53
West Virginia 1,813,077 5 362615.40 3.45 1.45
New Mexico 1,823,821 5 364764.20 3.47 1.44
Nevada 2,002,032 5 400406.40 3.81 1.31
Utah 2,236,714 5 447342.80 4.25 1.18

Arkansas 2,679,733 6 446622.17 5.09 1.18
<...>So:

(1) State population / EVs = Bloc per Elector
Total US population / Total EVs = National Bloc Size (not shown)
(I also have the average bloc size, but I'm not using it for this)
(2) State population / National bloc size = Proportional vote *
(3) EV / Proportional vote = Relative value[/FONT]
This is where the problem starts, with the extra .06 added to the 535 (DC is not in here). THEN, the numbers go even wonkier at the next step, when the electoral votes are divided by the proportional votes to get their relative value. I sort of know what the problem is here, but I'm not sure how to describe or fix it because I am stupid.


OK your formulas above look 100% correct. Can you mail me the worksheet? I think it would be easier for me to see what's going wrong. If your formulas are as described above, everything should work.

My email address is as follows (just remove all the # symbols because I put them there to foil automatic mail address scanners used by evil marketing companies):

f#arren@An#anzi.co.za

Shopping, BTW, isn't my forte. I'm still wearing the same jeans I bought four years ago and I've appropriated an old piece of crockery to serve as the cat's bowl after I lost that a couple of months back and never got round to replacing it. Why don't you see if someone's selling half a cutlery set on Ebay?