URGENT: Need Excel spreadsheet guru help, stat. Election related.

Jim March · Sep 7, 2006

Folks, I have a problem that needs fixing inside the next few hours if possible. This is directly related to making sure cheating didn't happen in an election. I'll fill in all the details later...I'm in Memphis Tennessee through tomorrow scoping out possible fraud in a recent election.

Upshot:

I have a spreadsheet of voting data that I don't think was tallied right. It was originally an MS-Access table, I've exported it.

The data looks like this: three columns of numbers, first column is the precinct, second column a candidate ID number (I know what they track to, that's in another table) and the third is the votes cast for each candidate in that precinct.

So let's say for simplicity's sake we have just one race, three candidates numbered one through three, two precincts labeled 40 and 41:

40 | 1 | 23
40 | 2 | 45
40 | 3 | 43
41 | 1 | 12
41 | 2 | 65
41 | 3 | 54

What I need is a set of formulas that will pick out the vote totals in column three for each candidate. In this case candidate 1 got 34 votes. I assume I need one formula per candidate showing what their totals were for the whole race, plucking both precinct number and candidate ID number to control totals based on THEIR numbers in the third column.

HELP!

Archangel · Sep 7, 2006

Not sure if this is the best way of doing it, but you could do it this way:

PM me if you can't get the zipped excel file or if you need more of an explanation.

Jim March · Sep 7, 2006

Oh no. Sorry guy, but the formatting wasn't created by me, it was done by Diebold, and the data is thousands and thousands of lines long. Ain't no way I'll have the time to hand-edit it out like that!!!

ceetee · Sep 7, 2006

Jim, there's probably some way to set up a "conditional" formula so that it will look through the second column, and every time it sees a "1" it will add those numbers, while every time it sees a "2" it will add those in a different cell. Do a little online research into "conditional" formulas. Microsoft has online help.

baz · Sep 7, 2006

Why not do a sort on column 2? That will arrange the rows by candidate. Then a sum function should be straight-forward.

taliv · Sep 7, 2006

there are dozens of ways to do this depending how what you're going to do with the data.

if you're just looking at it to verify it, i'd do something like this:
insert a blank row at the top and designate a block as your candidate cell. we'll call it cell B1, and we'll go ahead and put a 1 in the cell representing candidate that got thirty FIVE votes.

now in the fourth column (or the next available free column) we'll enter a formula and copy it all the way down. for the sake of argument, assume your data starts in row 2, so we'll enter this formula in cell d2. the formula that says something like this: =if(b2=$b$1,b2,0)

after copying that all the way down the spreadsheet, you should see a non-zero number for every row for candidate 1, and zero for every other candidate.

now in cell d1, we'll put a formula =sum(d2..d10000) (or wherever your data stops)

now, just enter a 2 in the candidate cell and then cell d1 will display the total for candidate 2 from all precincts. then change to a 3, etc.

this method requires you to change the number in cell b1 to see different candidates. if you want to see all the candidates at the same time, that's some more work. you can send me the spreadsheet or call me. and i'll try to help. i'll pm you my phone number

jwmiller · Sep 7, 2006

If your data is in three separate excel columns in the format you show, you can simple select all the data, then go to the "data" menu, and select sort. You will get the "sort" popup box. If you then sort by column B (candidate), then by column A (precinct) you will have all the data for candidate 1 on top, then 2, then 3. Then just select all the votes for candidate 1 and hit the sum button (sigma). Repeat the sum function for candidate 2 and 3. That seems to be the quickest and easiest way as long as I am not missing something.

RealGun · Sep 7, 2006

While someone may know of a way in Excel, if the sorting suggestions don't suffice, you would be better off working within Access, the better environment for selection criteria and report subtotalling. As a footnote, the best environment for data conditioning is actually Word, either text or tables.

emc · Sep 7, 2006

jwmiller beat me to it. His method would be the quickest and easiest way to sum things up by candidate.

FWIW,

emc

Waitone · Sep 7, 2006

Three level data sort.

Level 1--precinct
Level 2--candidate
Level 3--count

Paint data range with <end><curson direction>

Not automatic but it is fast.

junyo · Sep 7, 2006

In any modern version of Excel this is pretty strraightforward, just do a SUMIF function:
SUMIF(Range,Criteria,Sum_Range)
Where Range is the column to be evaluated, Criteria is the candidate number, and SumRange is the column to be totaled. And you could just sort the table above the sums by candidate to group candidate's counts by precinct.

Or just as easy, as RealGun suggests, reimport the data into Access and do a SQL aggregate function; Select Cloumn_2, Sum(Column_3), Group By Column_2. That will dump out all the totals at once. Or do a report that lists the data by precinct with a running total at the section footer to generate sums.

Jim March · Sep 7, 2006

OK. COOL. A sort on column two...DUH! Dang, I'm off my feed or something.

Failing that I'll look to more exotic solutions but I'd bet that would do it.

I'll fill y'all in later on what's up.

gc70 · Sep 7, 2006

A little late, but here's a general solution that produces a table of sums from column C based on the values in columns A and B.

Rename your existing datasheet "Data."
Create a new datasheet and place the following in the new datasheet:

put precinct numbers in column A, starting at cell A2
put candidate numbers in row 1, starting at cell B1
place the following array formula in cell B2

=SUM(IF(Data!$A$1:$A$50000=$A2,IF(Data!$B$1:$B$50000=B$1,Data!$C$1:$C$50000,0),0))

you have to press CONTROL, SHIFT, and ENTER to close an array formula
copy the array formula in cell B2 to other cells in the precinct/candidate matrix

(The formula evaluates: if the data in the 1st dataset meets the 1st condition and the data in the 2nd dataset meets the 2nd condition, then sum the corresponding data in the 3rd dataset.)

Your new datasheet will look like this:

__ 1 2 3 4 5
40 # # # # #
41 # # # # #
42 # # # # #

The formula is based on up to 50,000 entries in the columns of the original data and assumes the data starts in row 1 of each column.

The formula can be adapted to more than two dimensions/conditions, but it gets a little kludgy.

geekWithA.45 · Sep 7, 2006

Have you still got it in access?

If so, use SQL: (mine's a little rusty)

SELECT candidateID, SUM(votes) as "Votes"
FROM votingTable
GROUP BY candidateID;

assuming the column names are precinctID,candidateID,votes, and table is named votingTable.

It'll give you a nice table of whatcha want.

Spartacus451 · Sep 7, 2006

Does this still need resolution? If so let me know now, I could whip a java program or something in ten minutes to do it. If your running Unix I could do it in a shell script. You would export it to a comma separated value file and then run the java program on the CSV to do the tally. How could diebold screw up something as simple as an SQL query? If you had some scripting language available on the computer your running that would help. Sad that I would have to do that but I don't know Excel at all...

Hell... attach the data here if you can...

URGENT: Need Excel spreadsheet guru help, stat. Election related.

Jim March

Member

Archangel

Member

Attachments

Jim March

Member

ceetee

Member

baz

Member

taliv

Moderator

jwmiller

Member

RealGun

Member

emc

Member

Waitone

Member

junyo

Member

Jim March

Member

gc70

Member

geekWithA.45

Moderator Emeritus

Spartacus451

Member

Similar threads