Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 11 to 20 of 37

Thread: Help required for League Table data entry

  1. #11
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0


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

    Here's your amended spreadsheet.Table_02a.xlsx

  2. #12
    Hi,

    Thanks but I'm not sure how to wrap the formula in an IFERROR statement as you say. At the moment the cell contains "=(SQRT(J$54*1000)/(J4+1))*12.171612389"
    Could you show me what it should look like with the IFERROR. I'm not sure what a custom number format does either I'm afraid but I'd like the cells to have a dash in them if there is nothing in them. Is this possible.

    Cheers,

    Brian.

  3. #13
    Sorry Jeffrey, didn't notice you'd posted a revised table as it went to a second page on the forum. Same question applies though, how can I have the formula
    "=IFERROR((SQRT(H$54*1000)/(H4+1))*12.171612389,0)" return a dash instead of an empty cell?

  4. #14
    Is this correct?
    =IFERROR((SQRT(J$54*1000)/(J4+1))*12.171612389,"-")

  5. #15
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Yep, although instead of that, I left it as zero, and changed the custom number format to display a "-" instead of a zero using this custom number format
    #,##0;;-

    You enter these from the 'home' tab. So first you select your cells that you want the format to apply to. THen you click the little down arrow in the number format box and select 'more number formats' from the picklist (it's at the bottom). Then you select 'Custom' (again, at the bottom) and then you type you custom number format into the 'Type' box. In this case, its #,##0;;-

    A google search on custom number formats will explain these. I've done this in the attached file. Or you can simply use your approach. But again, my preference is to not replace zeros with text, but instead just change the way they display.Table_02b.xlsx
    Attached Files Attached Files

  6. #16
    I found the number formatting options but am happy to stick with "-" as long at it wont cause conflicts.

    I've updated the table and it's starting to look the way I want it. I added a simple formula to the red total games played column (G) and I think that's OK but I can't get the "-" option to work in the red total points column (C)... that just stays blank. Can that be fixed.

    I've also applied some conditional formatting to the first four places so that the cells are colored in but am not sure how to apply this to the other 9 dates without creating new rules for each column!

    If I can get those last two things sorted I think the table will be complete:-)

    Table_02c.xlsx

    Brian.

  7. #17
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi Brian. I see in your conditional format formula you have this for the 4th place:
    =H4=SMALL(H$4:H$53,4)
    If you're going to stick with that formula, then yes you have to duplicate the conditions.

    But given the range of numbers is always going to be 1 through to x - and given there will presumably always be a 1st, 2nd, 3rd, and 4th, then you can do away with the SMALL bit and just use this:
    =H4=4

    ...which means we can then extend the range that this conditional format applies to to other 'place' columns.
    So in the Applies To box you just change this:
    =$H$4:$H$53
    to this:
    =$H$4:$H$53,$J$4:$J$53,$L$4:$L$53,$N$4:$N$53,$P$4:$P$53,$R$4:$R$53,$T$4:$T$53,$V$4:$V$53,$X$4:$X$53,$Z$4:$Z$53

    And so on and so forth for the 1st, 2nd, and 3rd placegetters.
    =$H$4:$H$53,$J$4:$J$53,$L$4:$L$53,$N$4:$N$53,$P$4:$P$53,$R$4:$R$53,$T$4:$T$53,$V$4:$V$53,$X$4:$X$53,$Z$4:$Z$53

  8. #18
    That's brilliant, thanks... but I think I've cocked up somewhere as you'll be able to see straight away from this:
    Table_02d.xlsx

  9. #19
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Looks fine to me. Maybe your system is doing something mine isnt. Can you post a screenshot and explain what the problem is?

  10. #20
    Hi,

    The "Earnt" column seems to be adding values to the empty players that don't have any earnings!

    BTW, I fixed a problem with 4th place payout not being picked up in row 58 and added the second week of scores. I don't think either of those things caused this tho.


    Table_02e.xlsx

Page 2 of 4 FirstFirst 1 2 3 4 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
  •