Help required for League Table data entry

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

View attachment Table_02.xlsx

See attached.

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


part meant?

Is that to be applied anywhere?

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.

Perhaps in C68?

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

copied down and across the table

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:-(

I don't quite understand what you mean. Which formula? The Vlookup in H55 looks at H54, and then extracts from the table.

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.

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

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.

View attachment Table_02a.xlsx

Thanks again.

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)

Here's your amended spreadsheet.View attachment Table_02a.xlsx

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.

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?

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

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.View attachment Table_02b.xlsx

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

View attachment Table_02c.xlsx

Brian.

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

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

Looks fine to me. Maybe your system is doing something mine isnt. Can you post a screenshot and explain what the problem is?

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.


View attachment Table_02e.xlsx