Solved

Microsoft Excel - Need Help With A Scoresheet

Posted on 2013-02-06
14
339 Views
Last Modified: 2013-02-12
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
Comment
Question by:slingingshot15
  • 6
  • 6
  • 2
14 Comments
 
LVL 23

Expert Comment

by:NBVC
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

by:slingingshot15
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

by:Steve
ID: 38860400
Pretty close to the same thing. You would only have to edit the fields in yellow.
churches.xlsx
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:slingingshot15
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

by:NBVC
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

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

Author Comment

by:slingingshot15
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

by:NBVC
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

by:slingingshot15
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

by:
NBVC earned 500 total points
ID: 38863974
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
 

Author Comment

by:slingingshot15
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

by:NBVC
ID: 38864161
Thanks, I would appreciate that.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 38880082
Hi slingingshort15,  just wondering what your leader thought about the workbook?
0
 

Author Closing Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question