Raffle Entries

[grumpus]

I want to set up a contest raffle that awards more chances for those that get the correct response to a question earlier than others. For example, the contest is open for 10 days...those who respond correctly the first day would get 10 chances/entries in a random draw, those responding correctly on day 2 would get 9 chances/entries, and so on until those on responding on day 10 would get 1 chance/entry.


I'm stumped as to the best way to do this. Any suggestions as to automating the entry process other than copy/paste the same name on to X rows (depending on the number of chances/entries) and then using a RANDBETWEEN(1,X being number of entries) to choose the winner?


I'm using Excel 2007.


Thanks in advance for your help/suggestions.


Grumpus

 

[SirJB7]

Hi, grumpus!

Do you mean something like this?

https://dl.dropbox.com/u/60558749/Raffle%20Entries%20%28for%20grumpus%20at%20chandoo.org%29.xlsx

Regards!

 

[grumpus]

Thanks SirJB7 for taking a stab at it. I'm not sure I understand how that would apply to what I was looking to do. It may do so but I would need further explanation.


I uploaded a quick version of how I might envision it to look. I was hoping through the magic of excel formulas to simplify the process. If there is an easier way to do it without showing all the names, that would be okay too. Ultimately, all I would need is Columns A & B anything more feels like excess data.


http://speedy.sh/3aq5Q/Entries.xlsx


Hopefully this helps make things a little more clear.


Much appreciated,


Grumpus

 

[SirJB7]

Hi, grumpus!


Download again the updated file from same previous link. Look at the yellow areas with formulas to get your manual example:

C2: =SUMA(B$2:B2) -----> in english: =SUM(B$2:B2)

I2: =SI(FILA()-1<=MAX(C$2:C$6);FILA()-1;"") -----> in english: =IF(ROW()-1<=MAX(C$2:C$6),ROW()-1,"")

J2: =SI(I2<>"";INDICE(A$2:A$6;SI.ERROR(COINCIDIR(I2;C$2:C$6;1);0)+1-SIGNO(SI.ERROR(COINCIDIR(I2;C$2:C$6;0);0)));"") -----> in english: =IF(I2<>"",INDEX(A$2:A$6,IFERROR(MATCH(I2,C$2:C$6,1),0)+1-SIGN(IFERROR(MATCH(I2,C$2:C$6,0),0))),"")


Just advise if any issue.


Regards!


PS: BTW, check the red shadowed cell, was it only for confusing me a little or are you either English fan of Al Jones folk or Australian fan of Alan Jones F1?

 

[grumpus]

Thank you so much for your help!


This works great. I'll just update the formulas in Columns C, I and J to expand to the proper rows based upon the number of people in Column A because I can envision having a 100 participants or more.


The red-shadowed area is an error and is why I wanted to automate the process. Now I can enter the number of entries and they are correctly applied. Previously, I would have to manually copy and paste the entries and in the case of Al Jones, he should have had 4 entries but I manually copied 5...your solution eliminates that possible error.


Sorry, I don't know an Al Jones...I made that name up at random but I will Google him to learn more.


Thanks again,


Grumpus

 

[SirJB7]

Hi, grumpus!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: maybe you want to automatize it a bit more creating 2 dynamic named ranges for A2:A6 and C2:C6, so as avoid changing fomula in columns I and J each time you add a new entry in A:C. For first range: =DESREF($A$2,0,0,COUNTA($A:$A)-1,1)

PS2: advise if any trouble