• Status: Solved
• Priority: Medium
• Security: Public
• Views: 355

# 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
brian ramdhan
• 6
• 6
• 2
1 Solution

Commented:
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

iCT Technician Author 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

Commented:
Pretty close to the same thing. You would only have to edit the fields in yellow.
churches.xlsx
0

iCT Technician Author 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

Commented:
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

Commented:
There is a "Standing" in the third column on the Standings tab that ranks them.
0

iCT Technician Author 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

Commented:
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

iCT Technician Author 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

Commented:

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

iCT Technician Author 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

Commented:
Thanks, I would appreciate that.
0

Commented:
0

iCT Technician Author Commented:
thanks a lot man....they loved it....it worked well in our sports day yesterday....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.