muscadinejelli
New Member
- Joined
- Feb 20, 2015
- Messages
- 2
Hi everyone. Semi-lurker and I can find my way around a spreadsheet but not much of a programmer. That said, I've offered to try and write a spreadsheet that will count and determine the winner of the local Quilt Show this summer. I can make it work with a big enough hammer and an IF statement that looks like the Constitution of the United States, but I'd really appreciate something a wee bit more elegant.
18 categories of quilts.
Most have only 1-6 entries, a few might have as many as 10. Lap/Baby Quilts usually have the most.
Quilts are numbered as they are registered, i.e. Category 100 is King Size Bed Quilts, so the first five quilts hung up in that category would be 100,101,102,103,104. Category 200 is Queen Size Bed Quilts - 200,201,202,203. all the way through 1800 - Machine Embroidery - 1800,1801,1802,1803.
The ballots are vertical half sheets with each category and a line beside it, people take them around and vote on their favorites in each category, usually a couple of hundred ballots in total. (I live in rural Montana so the population pool is pretty small.) We need a clear 1st, 2nd and 3rd in each category. I believe ties drop the place: two firsts mean no second, I'll look into that. Then we need the overall Best In Show 1st, 2nd and 3rd. When the results are returned it would be really nice if it could return the name of the quilter associated with that quilt as well.
I have Excel 2016 on the laptop I'm using.
When I started this I set up a 'Data' sheet with the category labels across the top (as A100, A200, A300, set up as Names) and used Form for easy data entry from the ballot, I think that will work pretty well. I've set up a second sheet called 'Summary' that has a number from 1 to 10 running down the left side with 100,200,300 etc at the top and a '=Countif(A100_,10x)' in each cell. The intention was to find out how many of each number were selected, and I had some nebulous idea of how to determine which ones were highest. I realized along about cell D4 (=COUNTIF(A300_,303)) that I was probably taking a long way around something that must be much simpler. I'd intended to have a third sheet called "Winners" but didn't get that far.
I looked around the forum quite a bit, but the sales target and the sports ones that rank don't really seem to work for my categories.
Any ideas? The quilt show is in July but I'd like to take at least a draft to the March or April Quilt Show Committee meeting.
Thanking you in advance for any help and hoping it's an interesting problem for someone.
18 categories of quilts.
Most have only 1-6 entries, a few might have as many as 10. Lap/Baby Quilts usually have the most.
Quilts are numbered as they are registered, i.e. Category 100 is King Size Bed Quilts, so the first five quilts hung up in that category would be 100,101,102,103,104. Category 200 is Queen Size Bed Quilts - 200,201,202,203. all the way through 1800 - Machine Embroidery - 1800,1801,1802,1803.
The ballots are vertical half sheets with each category and a line beside it, people take them around and vote on their favorites in each category, usually a couple of hundred ballots in total. (I live in rural Montana so the population pool is pretty small.) We need a clear 1st, 2nd and 3rd in each category. I believe ties drop the place: two firsts mean no second, I'll look into that. Then we need the overall Best In Show 1st, 2nd and 3rd. When the results are returned it would be really nice if it could return the name of the quilter associated with that quilt as well.
I have Excel 2016 on the laptop I'm using.
When I started this I set up a 'Data' sheet with the category labels across the top (as A100, A200, A300, set up as Names) and used Form for easy data entry from the ballot, I think that will work pretty well. I've set up a second sheet called 'Summary' that has a number from 1 to 10 running down the left side with 100,200,300 etc at the top and a '=Countif(A100_,10x)' in each cell. The intention was to find out how many of each number were selected, and I had some nebulous idea of how to determine which ones were highest. I realized along about cell D4 (=COUNTIF(A300_,303)) that I was probably taking a long way around something that must be much simpler. I'd intended to have a third sheet called "Winners" but didn't get that far.
I looked around the forum quite a bit, but the sales target and the sports ones that rank don't really seem to work for my categories.
Any ideas? The quilt show is in July but I'd like to take at least a draft to the March or April Quilt Show Committee meeting.
Thanking you in advance for any help and hoping it's an interesting problem for someone.
