Solved

Microsoft Excel - Need Help With A Scoresheet

Posted on 2013-02-06
307 Views
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
Question by:slingingshot15
• 6
• 6
• 2

LVL 23

Expert Comment

ID: 38860344
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

Author Comment

ID: 38860379
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

LVL 7

Expert Comment

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

Author Comment

ID: 38860426
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

LVL 23

Expert Comment

ID: 38860648
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

LVL 7

Expert Comment

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

Author Comment

ID: 38862288
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

LVL 23

Expert Comment

ID: 38862482
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

Author Comment

ID: 38863844
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

LVL 23

Accepted Solution

NBVC earned 500 total points
ID: 38863974

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

Author Comment

ID: 38864118
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

LVL 23

Expert Comment

ID: 38864161
Thanks, I would appreciate that.
0

LVL 23

Expert Comment

ID: 38880082
0

Author Closing Comment

ID: 38882944
thanks a lot man....they loved it....it worked well in our sports day yesterday....
0

Featured Post

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file forâ€¦
This code takes an Excel list of URLâ€™s and adds a header titled â€śURL Listâ€ť. It then searches through all URLâ€™s in column â€śAâ€ť, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URLâ€™s are then highligâ€¦
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.