Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# EXCEL - I think? AUTOMATE SUMS

### #1 jayjay23 Posted 09 December 2009 - 05:31 AM

jayjay23

Member

• Member
• 85 posts
Hi all,

Thanks for any advice in advance, even if you just point me in the direction of some help.

I have a bit of an issue.
I run a soccer forum online and part of that is a game where everybody guesses the scores of games and scores points for correct results.
The problem i have is it takes forever because I do all the calculations manually in my head and then type them into excel.

I need a way to automate it as it really should be a simple task, anyone know what i can do?

here is a link to the table so you know what i mean...

http://bwfcboard.sen...rboard-t270.htm

example of what we do...

we each guess the scores (17 of us) , so for example...

Bolton V Man Utd 4 - 0
Arsenal V Chelsea 2 - 1
Liverpool V Man City 3 - 2

(but there are 17 sets of results and 10 matches per set)
see here for example http://bwfcboard.sen...week-4-t257.htm

and then we wait for the results which might be in reality

Bolton V Man Utd 1 - 3
Arsenal V Chelsea 2 - 1
Liverpool V Man City 1 - 0

so i then have to go through the list and manually compare each persons predicted score to the real score and allocate points (5 points exactly right, 3 points correct result and goal difference and 1 point correct winner)then I have to add all that up write it all down into a table and redraw the finished table each time in execel.

Prediction
Bolton V Man Utd 4 - 0
Arsenal V Chelsea 2 - 1
Liverpool V Man City 3 - 2

Actual Results
Bolton V Man Utd 1 - 3 0points
Arsenal V Chelsea 2 - 1 5points
Liverpool V Man City 1 - 0 3points

But I have to do it for 17 sets of at least ten matches. Takes 2 hours minimum!
Then do the table.

I am sure I could have something such as a little form for putting the scores into and get the computer to allocate the points and even arrange the table in order of most points first? no?

Thanks for any advice, even if you just point me in the direction of some help.

Edited by jayjay23, 09 December 2009 - 05:33 AM.

• 0

### #2 Jonesey Posted 10 December 2009 - 09:47 AM

Jonesey

Member

• Member
• 335 posts
I run something very similar, and believe me, it's a fairly big, complicated spreadsheet full of formulae, forms & macros.

You will need:

1 sheet for every person predicting - that's 17 in your case
Separate sheets for:

Fixture List (straight from the BBC's website)
A Summary sheet for the predictions
A sheet for actual results
A sheet for the League Table

A lot of this can be pre-populated (dates/fixtures etc) 'cos we konw in advance when games will be played.

Even with this system, I still have to copy & paste the predictions in every week, but the actual results are entered into a form.
• 0

### #3 jayjay23 Posted 14 December 2009 - 02:55 AM

jayjay23

Member

• Topic Starter
• Member
• 85 posts
so?
no easy way then?
er.
any tips on how i can find out how to set up the sheets?
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users