Remember Me?

#### Menu

#1
April 18th 08, 06:20 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 19
Formula for evaluating predictions - Fantasy Football League

I'm trying to do an Excel spreadsheet to work out the points awarded to the
participants of a Fantasy Football League (where you predict match scores
and get points based on the actual match result). I expect this has been
done many times using Excel but I wanted to have a go anyway!

I'm having problems evaluating part of the formula which works out the
points for each match. The part in question should evaluate if the predicted
result is correct but the score is not whether a home win, away win or a
draw. This is proving much harder to do than I thought when I said I'd try
and do it!

The scoring system will be 4 points if the result and score is predicted
correctly, 2 points if a draw is predicted but the score is incorrect and 1
point if the result is correct (apart from a draw) but the score is wrong.

The spreadsheet would have 4 columns for entering the predictions and the
results. Columns A and B would be for the home team and away teams predicted
score and columns C and D for the actual scores.

Does anyone have a clue what I'm even talking about - or hopefully have any
solutions? The more I think about it the more it's frying my brain :-)

#2
April 18th 08, 06:40 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 461
Formula for evaluating predictions - Fantasy Football League

Let me see if I get this.

Say you have

---A----B----C----D
1--2---5----3----4
2--1---1----3----3

So the first row is saying I predict the away team wins 5 to 2. The outcome
was the away team won 4 to 3. So in cell E or wherever you want you can right
up a formula.

=IF(AND(A1=C1,B1=D1),4,IF(AND(A1=B1,C1=D1),2,IF(OR (AND(A1B1,C1D1),AND(A1<B1,C1<D1)),1,0)))

If you put this formula in cell E1 or wherever you start the scores and then
copy and paste it down to the bottom it will evaluate all the games and give
you the scores.

"gavin" wrote:

I'm trying to do an Excel spreadsheet to work out the points awarded to the
participants of a Fantasy Football League (where you predict match scores
and get points based on the actual match result). I expect this has been
done many times using Excel but I wanted to have a go anyway!

I'm having problems evaluating part of the formula which works out the
points for each match. The part in question should evaluate if the predicted
result is correct but the score is not whether a home win, away win or a
draw. This is proving much harder to do than I thought when I said I'd try
and do it!

The scoring system will be 4 points if the result and score is predicted
correctly, 2 points if a draw is predicted but the score is incorrect and 1
point if the result is correct (apart from a draw) but the score is wrong.

The spreadsheet would have 4 columns for entering the predictions and the
results. Columns A and B would be for the home team and away teams predicted
score and columns C and D for the actual scores.

Does anyone have a clue what I'm even talking about - or hopefully have any
solutions? The more I think about it the more it's frying my brain :-)

#3
April 18th 08, 08:53 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 19
Formula for evaluating predictions - Fantasy Football League

"akphidelt" wrote in message
...
Let me see if I get this.

Say you have

---A----B----C----D
1--2---5----3----4
2--1---1----3----3

So the first row is saying I predict the away team wins 5 to 2. The
outcome
was the away team won 4 to 3. So in cell E or wherever you want you can
right
up a formula.

=IF(AND(A1=C1,B1=D1),4,IF(AND(A1=B1,C1=D1),2,IF(OR (AND(A1B1,C1D1),AND(A1<B1,C1<D1)),1,0)))

If you put this formula in cell E1 or wherever you start the scores and
then
copy and paste it down to the bottom it will evaluate all the games and
give
you the scores.

Thanks so much for that. I've just tried it on my home PC where I'm running
Open Office and I'm getting a #Name? error - I don't know if that is
peculiar to OO? I'm just about to fire up my work laptop which has Excel on
it and I'll report back.

Thanks again for your time.

#4
April 18th 08, 09:25 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 461
Formula for evaluating predictions - Fantasy Football League

No problem for the help. Im not very familiar with Open Office. I tested it
on an excel sheet and it worked perfectly. Let me know if you have any
problems in the actual excel sheet.

"gavin" wrote:

"akphidelt" wrote in message
...
Let me see if I get this.

Say you have

---A----B----C----D
1--2---5----3----4
2--1---1----3----3

So the first row is saying I predict the away team wins 5 to 2. The
outcome
was the away team won 4 to 3. So in cell E or wherever you want you can
right
up a formula.

=IF(AND(A1=C1,B1=D1),4,IF(AND(A1=B1,C1=D1),2,IF(OR (AND(A1B1,C1D1),AND(A1<B1,C1<D1)),1,0)))

If you put this formula in cell E1 or wherever you start the scores and
then
copy and paste it down to the bottom it will evaluate all the games and
give
you the scores.

Thanks so much for that. I've just tried it on my home PC where I'm running
Open Office and I'm getting a #Name? error - I don't know if that is
peculiar to OO? I'm just about to fire up my work laptop which has Excel on
it and I'll report back.

Thanks again for your time.

#5
April 18th 08, 10:36 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 19
Formula for evaluating predictions - Fantasy Football League

"akphidelt" wrote in message
...
No problem for the help. Im not very familiar with Open Office. I tested
it
on an excel sheet and it worked perfectly. Let me know if you have any
problems in the actual excel sheet.

Just tested on Excel on my laptop and it works perfectly. It didn't turn out
to be anywhere near as complicated as I feared - isn't that often the way???

Thank you so much for your time - it really is appreciated. I doubt I would
ever have got this working on my own!

#6
April 19th 08, 12:44 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 461
Formula for evaluating predictions - Fantasy Football League

Wow, glad it worked out. Yea it's amazing how things can be figured out in
excel. What's funny is some guys on here can probably do that same exact
equation that's half the size of mine.

"gavin" wrote:

"akphidelt" wrote in message
...
No problem for the help. Im not very familiar with Open Office. I tested
it
on an excel sheet and it worked perfectly. Let me know if you have any
problems in the actual excel sheet.

Just tested on Excel on my laptop and it works perfectly. It didn't turn out
to be anywhere near as complicated as I feared - isn't that often the way???

Thank you so much for your time - it really is appreciated. I doubt I would
ever have got this working on my own!

#7
April 19th 08, 07:53 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2008 Posts: 19
Formula for evaluating predictions - Fantasy Football League

"akphidelt" wrote in message
...
Wow, glad it worked out. Yea it's amazing how things can be figured out in
excel. What's funny is some guys on here can probably do that same exact
equation that's half the size of mine.

Well no one's posted an alternative so I reckon your solution is perfect!

#8
April 19th 08, 08:29 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Mar 2008 Posts: 913
Formula for evaluating predictions - Fantasy Football League

On Sat, 19 Apr 2008 18:53:53 GMT, "gavin"
wrote:

"akphidelt" wrote in message
...
Wow, glad it worked out. Yea it's amazing how things can be figured out in
excel. What's funny is some guys on here can probably do that same exact
equation that's half the size of mine.

Well no one's posted an alternative so I reckon your solution is perfect!

The following is a bit shorter, but the price for that is maybe less
clarity, so I suggest you stick with the formula suggested by
akphidelt.

=IF(AND(A1=C1,B1=D1),4,IF(AND(A1=B1,C1=D1),2,IF((A 1-B1)*(C1-D1)0,1,0)))

Lars-Åke

#9
June 10th 08, 09:03 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1
Euro Prediction league formula advice

I am doing a prediction competition however my points are slightly different and i am having trouble working out the formula. In my game, it is still 3 points for the correct score, however it is 1 point for the correct result.

For Example

Predicted Actual

cell A B C C

1 1 1 0 0

2 3 0 3 0

So game 1 would be 1 point as the draw was correctly predicted but the score incorrect.

I cant seemt to get the formula correct for this particular scenario.

Much appreciated if anyone can assist

Kevin
#10
July 4th 10, 02:41 AM
 Junior Member First recorded activity by ExcelBanter: Jul 2010 Posts: 1

Hi gavin and Akphidelt ,

i am new here and i saw your work-effort for evaluating score predictions in football league.

please, is there any formula for prediction Draw score in football for all major European leagues, not necessary finding the correct scores.

thanks for your reply

richard

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Worksheet Functions 1 June 29th 07 09:12 AM THawk Excel Discussion (Misc queries) 2 August 29th 06 09:33 PM wallisi Excel Discussion (Misc queries) 1 May 26th 06 09:24 PM tuggers Excel Discussion (Misc queries) 0 April 19th 06 06:31 AM Dave Excel Discussion (Misc queries) 0 July 8th 05 09:18 PM

All times are GMT +1. The time now is 10:21 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017