Help required for League Table data entry

[Brian Thomas]

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

[NBVC]

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?

[Brian Thomas]

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.

[NBVC]

Perhaps in C68?

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

copied down and across the table

[Brian Thomas]

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

[NBVC]

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

[Brian Thomas]

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.

[NBVC]

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

[Brian Thomas]

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.

[JeffreyWeir]

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)