Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Microsoft Excel - Need Help With A Scoresheet

Hi there.

Our church organisation is having its annual sports and I have to create an excel document to help calculate the scores etc....

baiscally the info i need to put in is

the various races and the names of each church

for every race if they come 1st they get 3 points, if they come 2nd, 2 points and 3rd is 1 point

there are probably about 12-15 churches

I want to be able to basically at the end of each event - put which church came first, 2nd and 3rd and give them the relevant points and i want there to be totals that keep adding up so i can see who is the overall winner at the moment and at the end without having to keep rechecking if you all understand....

if one of you could design something for me to look at it will be good.....
0
slingingshot15
Asked:
slingingshot15
  • 6
  • 6
  • 2
1 Solution
 
NBVCCommented:
See attached.

List the churches across the top.  List the events down the left.

Insert the scores as you go along.

At the bottom are rolling sums for each church.

To the right you see the top 3 scores/churches at any given time....
events.xls
0
 
slingingshot15Author Commented:
thanks a lot man.....it looks great....one thing i wanted to ask.....let's just say i want to edit it by adding a few more churches and a few more events without interrupting the calculations etc....how do i go about doing that???
0
 
SteveCommented:
Pretty close to the same thing. You would only have to edit the fields in yellow.
churches.xlsx
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
slingingshot15Author Commented:
sodea yours is nice but the only problem is there is no simple way for me to see who is first 2nd and 3rd at the moment if you know what  i mean.....it would be nice to see the places of churches......
0
 
NBVCCommented:
In my sample, if you insert above Church 14, the sums will adjust, if you insert above the Sum row, you will need to adjust the formula to accomodate the new rows.

If you add columns, you will need to extend the formulas to the right.

As I showed you can add events, churches, from the start and they don't necessarily have to be filled in...
0
 
SteveCommented:
There is a "Standing" in the third column on the Standings tab that ranks them.
0
 
slingingshot15Author Commented:
Hey NB....

I edited yours a little and i put 20 churches and 30 events....i was just wondering if you could edit the piece on the side with the correct formula so it could show the churches who are first 2nd and third, if i were to put in the other churches info....

thanks a lot man.....and you could make sure my autosum that i did was correct too.....
churches.xls
0
 
NBVCCommented:
Here you go.  It is just a matter of changing the M column reference to U column references.

And your sums at the bottom are correct.
churches.xls
0
 
slingingshot15Author Commented:
hey man i have a question

i notice that when i click one of the top 3 churches on the right and then after if i click the formula bar and then click out of the formula bar you get this #VALUE error

i realise a simple undo will get things back to normal.... but was jus wondering why this happeneed...

Also I am sorry to be a little pain but I just got info that it is 21 churches so if you just include the 21st church that will be good...

that is all i need and the info will be perfect and i will close this thread....

i not to sure how to edit the formula as you said in previous post.....that looks like an extremely complicated formula you put in.....

once again thanks for your help.....
0
 
NBVCCommented:
I have updated your workbook to add the church.

If you need to add more, I would insert a column before the last church, then all you have to do is copy the sum row formula to the bottom of that column.  The ranking formulas will self adjust.

Then formula to show the top churches is a special ARRAY formula.  I used it here because in the likelihood of there being 2 churches with same top score, then both churches would be listed.  If I used a simple Matching formula, then it would repeat the same church twice.

With Array formulas, you need to confirm it in a special way; that is to hold the CTRL and SHIFT keys down, then hit ENTER.  You will notice that this adds the { } brackets around the formula.

Here is more on Array Formulas.
Copy-of-churches-1.xls
0
 
slingingshot15Author Commented:
i'm going to email it to the leader for him to look at and will get back to you...

thanks a lot man.....
0
 
NBVCCommented:
Thanks, I would appreciate that.
0
 
NBVCCommented:
Hi slingingshort15,  just wondering what your leader thought about the workbook?
0
 
slingingshot15Author Commented:
thanks a lot man....they loved it....it worked well in our sports day yesterday....
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now