Need help with a formula

Brian Thomas

New member
Joined
Nov 27, 2012
Messages
30
Reaction score
0
Points
0
Hi. After wrestling with my spreadsheet for a few hours I have had to admit defeat and seek help here!

I run a poker league and keep the table updated via the attached spreadsheet which someone helped me make. I'm no longer in touch with them and can't edit it myself and would be really grateful if someone could help me with the following formula.

I would like the cell U6 to calculate the following:

Count the total number of players on Monday 5/11/12 then work out from the points I have entered at I6-I29 who came 1st, 2nd, 3rd and 4th. It does this at the moment but I need it to copy the relevant payout information from the red, green, blue and peach rows below the main table. This is because sometimes the nights "pot" is divided between the top two finishers and at the moment that means the "Earnings" cell is sometimes incorrect as it calls information from the lookup table at the foot of the page. What I'd like to be able to do is just manually enter the split amount in the colored cells in the table so that the published table shows the correct prizes as well as the correct earnings (which means the cell U6 and all the others need to draw their info from the colored cells)

I hope this makes some sense. Any questions please ask.


Many, many thanks in advance,

Brian.
 

Attachments

  • league table BETA.xls
    161.5 KB · Views: 12
Can you post an amended workbook where you put your desired results in manually for a couple of the weeks, I am not absolutely sure what you are asking?

Also, shouldn't you be using conditional formatting for that colouring in I6:R55, I assume you do it manually now.
 
Hi Bob,

Thanks for looking. I've attached the amended workbook. If you look at week 3 I have manually changed the first and second place prize money payouts but this hasn't been picked up in the "Earnings" column for the players concerned as these figures come from the look-up table (bottom of page) via the table on the right of the sheet. I'd like the values for the "Earnings" to be drawn from the coloured rows beneath the results. That way, when there's a split between two players and the payout changes, I can manually input it for that week and the "Earnings" will still be correct.

Yes I manually change the colour of the cells to show the top 4 each week. Is it possible to have this automated?

Many thanks,

Brian.
 

Attachments

  • league table BETA2.xlsx
    50.4 KB · Views: 12
Brian,


I changed the formula in U5 to

=IF(ISTEXT(I5),"-",IF(I5>=LARGE(I$5:I$54,4),LARGE(I$56:I$59,COUNTIF(I$5:I$54,">="&I5)),0))

which you can copy down and across.

The attached file also shows how to automate the colouring.
 

Attachments

  • XLGuru - 1268 - league table BETA2.xlsx
    50.5 KB · Views: 13
Bob,

Thank you so much. I'm just going to look over what you've done in an effort to try and understand it a bit better!


Brian.
 
Bob,

Just wanted to say a huge thanks for sorting that for me. I was up for ages last night, trying to do it myself but I have very little excel knowledge and didn't stand a chance really.
I'm really happy that updating the results every week will be that much quicker and a lot more accurate.

I managed to find the conditional formatting that you added and seeing the "LARGE" formula there, rather than surrounded by other figures, helps me understand what it is you did so hopefully I've learned something too.

All the best,

Brian.
 
Posting to get my message count up to 5 in order to post an email address in the next message!
 
Hi Bob,

I have a query/proposal for you and tried to send a direct message but can't as I haven't posted enough. Would you drop me a line at admin@oldtownpoker.co.uk so I can write to you directly? It's just a request to do some work on my league table website but I didn't want to post it here.

Many thanks,

Brian.
 
Back
Top