+ Reply to Thread
Results 1 to 15 of 15

competition where the no of entrants determines determines the number of winners and prize

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    9

    competition where the no of entrants determines determines the number of winners and prize

    i have a weekly competition where any number of entrants may turn up. if up to 23 play then there are three prizes (50%,30% and 20%)

    If 24 to 32 play there are four prizes (40%.30%.20%.10%)

    33 to 38 five prizes £25 max £20max £15max £10max £6 max

    The number of payers is automatically counted when the scores are added and the entry fee is £2

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    OK - sounds like fun. What's your question?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: competition where the no of entrants determines determines the number of winners and p

    Sorry how can i construct a formula that works out the prizes for each number of entrants?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    It will depend how you have structured your data entry. Some idea of data layout and collation would help.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    How about this?

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Players
    Prizes
    1st
    2nd
    3rd
    4th
    5th
    2
    0
    3
    50
    30
    20
    3
    24
    4
    40
    30
    20
    10
    4
    33
    5
    33
    26
    20
    13
    8
    5
    6
    7
    8
    Players:
    24
    1st 2nd 3rd 4th 5th
    9
    Prize Fund:
    48
    £ 19.20
    £ 14.40
    £ 9.60
    £ 4.80
    £ -
    10
    Prizes:
    4
    Sheet: Sheet1

    C9: =2*C8

    C10: =VLOOKUP(C8,$A$2:$B$4,2,1)

    E9 cop[ied across: =$C$9/100*INDEX(D$2:D$4,MATCH($C10,$B$2:$B$4,0))

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    Forgot to attach the workbook.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: competition where the no of entrants determines determines the number of winners and p

    Beaten to it by Ali:-)

    Mine is just a different format, same result.
    Attached Files Attached Files
    Uploading an attachment will assist Forum Members in finding the correct Solution for you. Create a dummy worksheet if necessary, add your start data and show where / what your expected results should be. TO ADD A FILE:

    Go to 'Advanced. click on 'Manage Attachments. Select 'Browse & Choose your file, Select 'Upload and then close the Attachment window.

  8. #8
    Registered User
    Join Date
    07-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: competition where the no of entrants determines determines the number of winners and p

    Hi I have not added the data input form yet so the scores go into Column D as a manual.

    i have attached an example of the shhet with scores. the sheet calculates the position of up to six places.
    sixth place and blanks those above.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    BlindAlley - Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    Howard - you haven't attached anything. However, we (myself and BlindAlley) have each posted solutions for you. I suggest you go with BlindAlley's as it's more comprehensive, but you might find my simpler approach interesting.

  11. #11
    Registered User
    Join Date
    07-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: competition where the no of entrants determines determines the number of winners and p

    Thank you both. i realise now that i should have seperated the question about prizes from the player list and scores.
    Both the answers help. i think that BlindAlley will help me a little bit more as i can use vlookup to return the prize won to to each player.

    As a bit of background this is a golf competition which takes place each week and there can be 50+ entrants. Automating the score and prize will save a lot of time. my next step is to take the scores and add the to the player list. Here, if the player won, they are penalised shots for a number of weeks (based on thier playing category [golf has four depending on handicap] and which prize they achieved. Then if they win again whilst under penalty the penalty is doubled. This makes for one of the fairest competitions in golf.

    Thanks again for your promts replys and the neat solutions.

  12. #12
    Registered User
    Join Date
    07-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: competition where the no of entrants determines determines the number of winners and p

    Sorry i thought i had attached the file.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  14. #14
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: competition where the no of entrants determines determines the number of winners and p

    Apologies Ali,

    After seeing your post I didn't give that a thought.

    (Perhaps there should be a rule that says the 1st helper should reply (Working on it)

    Steve
    Last edited by BlindAlley; 07-10-2019 at 10:54 AM.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: competition where the no of entrants determines determines the number of winners and p

    No worries - just bear it in mind next time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] quantity determines price
    By gocolonel77 in forum Excel General
    Replies: 1
    Last Post: 01-06-2019, 08:19 PM
  2. I need to find a formula that determines the low of the number in a range
    By Markham in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-18-2014, 01:38 PM
  3. Help with sending iTunes prize codes to 100 or so winners
    By iminoorali in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-30-2013, 07:34 PM
  4. July Competition Winners
    By Vaibhav in forum Excel Tips
    Replies: 9
    Last Post: 08-13-2012, 12:47 PM
  5. Winners of July competition?
    By pike in forum The Water Cooler
    Replies: 2
    Last Post: 08-08-2012, 05:07 AM
  6. Variable Determines Number of Cell in Formula Range
    By MJSlattery in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-29-2006, 08:28 PM
  7. Function that determines whether a number is between two numbers
    By Jedispiff in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2006, 12:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1