+ Reply to Thread
Results 1 to 10 of 10

I record a teams score and I need the reverse score recorded for other team

  1. #1
    Registered User
    Join Date
    01-18-2016
    Location
    Mt Maunganui, New Zealand
    MS-Off Ver
    2007
    Posts
    5

    Post I record a teams score and I need the reverse score recorded for other team

    Using Excel, I record the results of a Bowls Tourney. Each round a team plays a different opponent on a different rink.
    Currently I record the results for each opposing team.
    Eg. Team A plays Team B on Rink 1
    Result for Team A. Win. Points For 10; Points Against 8
    Result for Team B. Loss. Points For 8; Points Against 10
    Up to 16 teams can participate in each round.
    What I wish for is a formula that will pick up Team As result and reverse it for Team B.
    Relevant Columns are;
    Team Name
    Points For
    Points Against
    Rink Number
    Sample File attached
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: I record a teams score and I need the reverse score recorded for other team

    Hi Kenbola,

    Welcome to the forum...

    Bearing in mind that you will have to manually enter the results for the first team in each pair, could you not simply do something like this?

    Bowls.jpg
    Thanks,
    HangMan

    You can say "Thank you!" by clicking Add Reputation below the post.
    Please, mark your thread [SOLVED] if you are happy with the solution.

  3. #3
    Registered User
    Join Date
    01-18-2016
    Location
    Mt Maunganui, New Zealand
    MS-Off Ver
    2007
    Posts
    5

    Re: I record a teams score and I need the reverse score recorded for other team

    Thank you Hangman.
    I could of course but a tournament can consist of over a hundred teams playing up to eight games over several days - it would be quicker just to enter both results separately. What I am looking for is a formula that I can paste into all the 'for' and 'against' columns.

  4. #4
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: I record a teams score and I need the reverse score recorded for other team

    Hi Kenbola, will this work for you?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-18-2016
    Location
    Mt Maunganui, New Zealand
    MS-Off Ver
    2007
    Posts
    5

    Re: I record a teams score and I need the reverse score recorded for other team

    Thanks Beamer NSW,
    I believe this is approaching a solution but would be cumbersome to introduce to my system. I need two formulas that I can paste into the two columns Points For and Against. For Team A the formulas would be overwritten by the actual result. For Team B the reverse result would be recorded using only the result for Team A and the Rink number column.
    Possibly the file I posted was too simplistic so attached is an actual example file, a medium sized event involving 62 teams playing six rounds each. Macros have been deleted as not relevant to my enquiry. The time saved would be pretty nominal (62 data entries per round) so my enquiry is more out of curiosity than practical so I apologise if I am wasting people's time..
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: I record a teams score and I need the reverse score recorded for other team

    You're right, that is a lot different to the original file.
    I don't see anywhere that tells me team A or team B.
    But...
    This next formula entered in cell K4 and copied down would mean you don't need to enter the Agst numbers, they will be filled automatically from the For column (J).

    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: I record a teams score and I need the reverse score recorded for other team

    Actually, I think this might be what you are looking for.
    Although, if you sort the sheet I think it will break.

    Cell J4: =IFERROR(INDEX(K:K,MATCH(AU4,$AU$1:$AU3,0)),0)
    Cell K4: =IFERROR(INDEX(J:J,MATCH(AU4,$AU$1:$AU3,0)),0)

    Copied down will fill the cells with 0. As you enter For & Agst for 1 player it will autofill the other player.

    You just need to re-copy the formula over the cells at the start of each comp.

    Edit: changed "" to 0 in formulas to save errors.
    Last edited by Beamernsw; 01-19-2016 at 09:24 AM.

  8. #8
    Registered User
    Join Date
    01-18-2016
    Location
    Mt Maunganui, New Zealand
    MS-Off Ver
    2007
    Posts
    5

    Re: I record a teams score and I need the reverse score recorded for other team

    Your last reply solved the problem. Only seems to work when the result entered is higher in the column but as this is the way results are generally provided to me it is not a problem. Sorting is always done on a separate sheet with the data Paste Special/Values so no formulas involved. Trying to fully understand the formula, do J:J and K:K reference the whole column?
    Thanks for your efforts.

  9. #9
    Registered User
    Join Date
    01-18-2016
    Location
    Mt Maunganui, New Zealand
    MS-Off Ver
    2007
    Posts
    5

    Re: I record a teams score and I need the reverse score recorded for other team

    Your last reply solved the problem. Only seems to work when the result entered is higher in the column but as this is the way results are generally provided to me it is not a problem. Sorting is always done on a separate sheet with the data Paste Special/Values so no formulas involved. Trying to fully understand the formula, do J:J and K:K reference the whole column?
    Thanks for your efforts.

  10. #10
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: I record a teams score and I need the reverse score recorded for other team

    Hey Kenbola,
    Only seems to work when the result entered is higher in the column
    That is correct. It is only looking for a match in the rows above. e.g. in J7 the match is looking at rows 1 to 6 to see if there is a matching Rink No. --- MATCH(AU7,$AU$1:$AU6,0)
    So, yes...when you enter a score in the upper row of, say Rink A1, then the matching A1 below will see it and fill in the details.

    do J:J and K:K reference the whole column
    Yes that is also correct

    So essentially, cells in J and K of each row are looking for their matching rink no. in column AU above their existing row. If they find one they will grab the matching row from columns K & J respectively.

    Hope that helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [solved] Team score look-up ? Please help!!
    By martymart2u in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2013, 01:44 PM
  2. excel to calculate golf teams by average score ?
    By mstoney in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-05-2013, 06:43 PM
  3. Replies: 6
    Last Post: 06-04-2013, 03:16 PM
  4. Moving a calclated score to a master score sheet and ranking the scores into placings
    By Jongleur69 in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 04-22-2013, 11:53 PM
  5. Both teams to score
    By khanaran in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-28-2012, 02:01 AM
  6. Replies: 2
    Last Post: 03-20-2012, 06:30 PM
  7. Replies: 1
    Last Post: 03-23-2006, 05:43 PM
  8. [SOLVED] Highest score in team
    By Hugh Murfitt in forum Excel General
    Replies: 4
    Last Post: 06-16-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1