How to set up a score card for your Iron Chef party (or tabulate other voting/scoring situations) in OpenOffice Calc spreadsheets

Here's the point where MAX, COUNTIF, data validity, and conditional formatting, come together to tabulate voting results. The project also involves chocolate and squash.

I'm throwing an Iron Chef party pretty soon. It's potluck style -- I've designated the "secret" ingredients, squash and chocolate (which need not be used together) and people will bring a dish using one of them. After we eat, we'll vote for the best squash dish, best chocolate dish, best overall, most original, and best imitation of Alton Brown or Chairman Kaga.

With up to 30 people coming, though, I don't want to be in my office tabulating paper ballots while the revelry continues in the rest of the house.

So I thought to myself, how can I set up a cool (aka geeky) spreadsheet so that people can just type in their votes and the winners will just reveal themselves?

I think I'm actually end up going to use a database and a form. But this is a decent approach too, and the tabulation process (since I don't do a lot of SQL coding) is the same.

Here's the situation. I'm condensing it a bit to make it simpler but this is the essential. I've got 10 people coming, and seven people: Bob, Ellen, and others, are actually making dishes. All 10 people need to vote, reasonably anonymously, for the best dish in each of three categories. Each person randomly chooses to vote as Voter1, Voter5, whatever. They choose the person who created the best dish from the dropdown list.

That's how we start. How do you create it?

(To just download it, click here.)

Type the Voter1-Voter10, and the headings for the three prizes.

Switch to Sheet2 (just to keep Sheet2 clean) and type in all the names of the people who cooked dishes.

Now in the first blank cell under the first heading, next to Voter1, choose Data > Validity.

You're going to use the Cell List feature to create a dropdown list with whatever values are in the cell range you specify. Use absolute references as shown to refer to the range of cells in Sheet2 that contain the names.

Click OK.

The list will look something like this.

Use the small black square to drag the dropdown list cell to the other cells that need dropdown lists.

You're all set. Now let's say it's party time and people have made their votes. Or at least this is what it would look like.

Now let's set up the tabulation. First, bring in the range from Sheet2 so it's easy to see which person got the number of votes. Just type =Sheet2.A1  and drag down.

Now you're going to use the =COUNTIF() function to count, for each category/name combination, the number of votes for each member.  It's =COUNTIF(RANGE;VALUE) or, for the first one, =COUNTIF(B$2:B$11;$A16)   Be sure to use absolute references for some of the values as shown so you can drag the values to save time and effort.

Now drag the value down, and over, to fill all the cells for each name/category combination.

Now, you'll use the =MAX() function to pull out the biggest number in each column.

This is the result.

Now, you can either look for the number that MAX() gives you, to find the winner. That's all you'd need to do once all the guests have voted.

Or you could use Conditional Formatting to highlight the number that is equal to the MAX value.

Select the first column and choose Format > Conditional Formatting.

Set up the window as shown. You're going to make the cell that is equal to the MAX cell formatted using the Heading style.

Do the same for the other two columns, referencing the MAX cell corresponding to each.

Now that you've got this all set up, you'd just delete the values out of the voting table so it's blank. Then set it up on your laptop at your next Iron Chef party, and let people vote. The totals will automatically be calculated, and the winner for each category (or the people tying for first, as with one of the categories here) will be automatically highlighted. All you have to do is look at the spreadsheet and pass out the prizes.

Comments

[wow power leveling]

http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/

[metin2 yang]

With up to 30 people coming, though, I don't want to be in my office tabulating paper ballots while the revelry continues in the rest of the house.

[metin2 yang]

　　第十七种调戏法：

　　“我喜欢的前戏是长久的爱抚和按摩。我特别喜欢对方轻轻爱抚我的腹部一直到大腿、肋骨两侧以及颈部。大部分女人太用力抚弄我的阴茎，我喜欢女人在前戏以及性交时快乐的呻吟。”

　　第十八种调戏法：

　　“我喜欢感觉到情人整个身体压在我身上。”

　　第十九种调戏法：

　　“我喜欢对方按摩我的背部，我能够感觉到背部的任何抚触。”