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

1. 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

2. 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

3. 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. Re: I record a teams score and I need the reverse score recorded for other team

Hi Kenbola, will this work for you?

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..

6. 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. 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.

8. 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?

9. 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?

10. 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

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

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