Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 37

Thread: Help required for League Table data entry

  1. #1

    Help required for League Table data entry



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    If someone out there would be willing to help me get the following set up to my specification I'd be more than happy to PayPal them the cost of a nice bottle of wine or a case of beer! If you think you can help please let me know.

    -----------

    I run a small poker league and enter the results each week into a table that I had some help designing recently. I now want to apply a formula that I've been given to the results each week that will improve the way we determine the league champion. I have very little knowledge of Excel and would appreciate it if someone could help set up this formula and the rest of the worksheet in such a way that I just enter the results each week and everything else is done automatically. I've attached a file that features two worksheets. The first is the old one which features all the formula's, look up tables and conditional formatting. The second is just a template with all formula's etc. removed. It's basically how I'd like the table to look when finished. I've pasted the new formula that should calculate the scores below the table. The first weeks results have all been entered manually. The part of the original table that calculates earnings has been left off the new worksheet as having added new columns for both place and points, the formula gave errors which I couldn't fix so this also needs to be updated!

    Many thanks,

    Brian

    Table_02.xlsx
    Last edited by Brian Thomas; 2013-06-18 at 12:10 PM.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    See attached.

    Not sure if that completes your requirement. If not let me know what's missing. I didn't know what the

    formula: =(SQRT(A1*1000)/(A2+1))*12.171612389
    Where A1 = the number of runners.
    Where A2 = the finishing position.

    part meant?

    Is that to be applied anywhere?
    Attached Files Attached Files


  3. #3
    Hi,

    Thanks for looking and helping. The formula needs to go into the "points" cell.

    For instance:
    I enter the players finishing position in to cell H4 and the formula in cell I4 looks to see how many players there were (H-I54), then to the finishing position and then enters the points. The table here was created with the formula and I thought it might help to see it. Number of players runs across the top and their points run from top to bottom.
    http://www.oldtownpoker.co.uk/new-points-structure/

    Many thanks,

    Brian.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Perhaps in C68?

    =(SQRT($B68*1000)/(LEFT(C$67)+1))*12.171612389

    copied down and across the table


  5. #5
    Hi,

    I don't think the formula should be looking to the payout table below.

    The first part should look to the cell H54 to see how many players there were and the second part should look the the cell on the left of the formula to see what position the player finished. I've tried to do this but it's beyond me and I get errors:-(

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    I don't quite understand what you mean. Which formula? The Vlookup in H55 looks at H54, and then extracts from the table.


  7. #7
    Hi,

    Yes, that formula works out the payout and it works fine thanks. The formula I mean is the one I was given as "=(SQRT(A1*1000)/(A2+1))*12.171612389". This should go into cell I4 and the A1 bit should be changed to call the value from H54 (number of entrants) and the the A2 bit should call the number from H4 (place). This should mean that all I have to enter is each players finishing position in the H column labelled "place" and then the cell to the right will work out how many points they got. Sorry if I'm not explaining myself very well but I'm really grateful for the help.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Then, in I4:

    =(SQRT(H$54*1000)/(H4+1))*12.171612389

    copied down

    Then copy this column to all the other "points" columns.

    Note: to avoid circular referencing, change formula in H54 to:

    =COUNTIF(H4:H21,">0")

    copied across


  9. #9
    Hi,

    That's great. The only problem seems to be in the cells where there aren't any numbers. I just get lots of hash tags. Is there a way to replace these with dashes if the cell is empty?
    I've attached the new version of the table.

    Table_02a.xlsx

    Thanks again.

  10. #10
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi Brian. Those hash tags are just Excel's way of telling you that the formula result is wider than the current column width allows, so Excel can't display it. If you increase the column width, you'll see that these actually display as #VALUE! errors. To get rid of these, you can wrap all the offending formulas in an IFERROR statement:
    =IFERROR(SomeFormula,0)
    ...and then apply a custom numebr format to the cell that suppresses zeros:
    #,##0;;

    Or you can have the IFERROR return a blank and do away with the custom number format (but this can screw with other formuals, which is why I usually recommend you instead return zero as per above, and hide the zero with custom number format):
    =IFERROR(SomeFormula,0)

Page 1 of 4 1 2 3 ... LastLast

Posting Permissions

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