Is Excel capable of doing this?

I’m looking to automate some scoresheets. I know nothing of Excel, although I don’t mind learning it in order to get what I want. But I’d hate to spend several days learning and then find out that the program can’t do what I need it to.

So anyway, here it is.

The first part is pretty simple, and I’m sure Excel can do it: Keep a running total as I enter scores after each round.

Team A…Team B…Team C…Team D
…1…2…2…0
…1…2…2…0

Team A…Team B…Team C…Team D
…1…2…2…0
…2…3…1…1

…3…5…3…1

Team A…Team B…Team C…Team D
…1…2…2…0
…2…3…1…1
…1…2…2…0
…4…7…5…1

It’s the next part I’m not sure of. I need a ranking of the teams and their cumulative scores. One way to do that, which I doubt is possible, is to reorder the columns based on the value in the bottom row:

Team B…Team C…Team A…Team D
…2…2…1…0
…3…1…2…1
…2…2…1…0
…7…5…4…1

Another way would be to create a new array with the elements from the top row and the bottom row, ranked by the bottom row.

Team A…Team B…Team C…Team D…Team B…Team C…Team A…Team D
…1…2…2…0…7…5…4…1
…2…3…1…1
…1…2…2…0
…4…7…5…1
Of course, there may be more ways I’m not thinking of right now.
NOTES:

  1. Sorry about the dots. I couldn’t come up with another way to get the columns to line up.
  2. Sorry the post is so long.
  3. I need the ranking at only three points during the game, so it can be a function that requires keystroking rather than something that happens automatically as I enter the scores.
  4. Is this possible in Excel?
  5. I’m not asking anyone to explain to me how to do it. Just tell me it can be done, and I’ll figure it out.
  6. Thank you in advance.

It is possible.

For the first part, use the SUM function. This is trivial if you have a fixed number of rows with the sums at the bottom, and you gradually fill in the rows.

In later versions of Excel (2007+), you can sort left-to-right, so you can do the second part by sorting.

For future reference, when you want to post something with specific spacing, use CODE tags around it.



A      B       C
1      2       3


Excel has a function called RANK that you can use. You’ll need to account for tie scores when you display things. Yes, you can do what you want in Excel, but it will take some learning. First, try a web search for an Excel spreadsheet score card for the game/event you want to track. Someone may have done the work already!

Do you have to physically move the columns to be in order? If you can leave them where they are, and indicate their rank, I would do it with conditional formatting. Select the row of totals, click on Conditional Formatting, Color Scales, and the choose one of the options. The Red-Green Scale would turn the top score green, the middle scores various shades of yellow, and the bottom score red.

If you know how many rows you will be adding, you can put your SUM function after the final row. So if you know you are going to have, say, 25 scores to add up, you would put your column name in Row 1, then your scores in rows 2-26, and your sum function “=sum(a2:a26)” in A27.

But if you don’t know how many rows you will end up with, you can always put your total at the top of the row. So A1 would say “Team A”, A2 would be “=sum(a3:a27)” and then your scores would follow.

You could use the RANK function to sort, but I would probably just list the teams to the side with their scores and then use the Data-Sort menu tool. So, you use Columns A-D for your scores, then in F1:G4 you do



    F           G
-----------------    
1| Team A    =sum(A2:A26)
2| Team B    =sum(B2:B26)
3| Team C    =sum(C2:C26)
4| Team D    =sum(D2:D26)

Then select this range, go to the Sort command on the Data menu, and sort by column G.

Thank you all for the help.

Here’s how I’d do it:



Team    Round 1  Round 2  Round 3  Total
A       1        2        2        4	
B       2        3        2        7
C       2        1        1        5
D       0        1        0        1


Then you just sort by Total.