You are Here: Home

# Excel formula Watch

Announcements
1. I am currently creating a Premier League predictor competition through a project at University, where I am asking people to send me their final league table predictions. I am running this in excel, and have set the terms that for every position that they have a team out they lose -1 points, so if they predict a team to finish 1st and they finish 5th they are 4 positions out, so they would have -4 points. If they get a team right then they get 10 points.

Has anyone got a formula (I am guessing it would be an IF statement saying IF "Team A" = "Team B" then.....) that can automatically show me the scores to save me updating every teams scores?

I have the current league table displayed, and then every persons predictions along side that (which will hopefully make it easier). All of the teams are written in the same format as well.
2. I would just put the teams in alphabetical order in row a, then the prediction numbers "1-20" in row b and then the final numbers in row c. Then do ROW B - ROW C. Of course, as you want all the differences to be negative you then do an if statement along the lines of IF difference column is > 0 *-1. And IF difference column equals 0, assign that cell a score of 10.

Been a long time sinces I've used excell properly but I hope that helps.
3. Thank you. I managed to have a play with it and ended up doing a ridiculously long IF statement which consisted of:

=IF(C3=\$A\$3,"10",IF(C3=\$A\$4,"-1",IF(C3=\$A\$5,"-2",IF(C3=\$A\$6,"-3",IF(C3=\$A\$7,"-4",IF(C3=\$A\$8,"-5",IF(C3=\$A\$9,"-6",IF(C3=\$A\$10,"-7",IF(C3=\$A\$11,"-8",IF(C3=\$A\$12,"-9",IF(C3=\$A\$13,"-10",IF(C3=\$A\$14,"-11",IF(C3=\$A\$15,"-12",IF(C3=\$A\$16,"-13",IF(C3=\$A\$17,"-14",IF(C3=\$A\$18,"-15",IF(C3=\$A\$19,"-16",IF(C3=\$A\$20,"-17",IF(C3=\$A\$21,"-18",IF(C3=\$A\$22,"-19",))))))))))))))))))))

Which seems to do exactly what I want
4. (Original post by foster187)
Thank you. I managed to have a play with it and ended up doing a ridiculously long IF statement which consisted of:

=IF(C3=\$A\$3,"10",IF(C3=\$A\$4,"-1",IF(C3=\$A\$5,"-2",IF(C3=\$A\$6,"-3",IF(C3=\$A\$7,"-4",IF(C3=\$A\$8,"-5",IF(C3=\$A\$9,"-6",IF(C3=\$A\$10,"-7",IF(C3=\$A\$11,"-8",IF(C3=\$A\$12,"-9",IF(C3=\$A\$13,"-10",IF(C3=\$A\$14,"-11",IF(C3=\$A\$15,"-12",IF(C3=\$A\$16,"-13",IF(C3=\$A\$17,"-14",IF(C3=\$A\$18,"-15",IF(C3=\$A\$19,"-16",IF(C3=\$A\$20,"-17",IF(C3=\$A\$21,"-18",IF(C3=\$A\$22,"-19",))))))))))))))))))))

Which seems to do exactly what I want
Well... It's not elegant, but it works!!
5. Can we see a screen grab of the data?

Curious as to how much you've overcomplicated it.
6. (Original post by S27)
Well... It's not elegant, but it works!!
I know! If anyone has a way a tidying that up then that would be great!
7. (Original post by foster187)
I know! If anyone has a way a tidying that up then that would be great!
Simple. Have three tables. One with columns for teams and rows for people with the predicted placing.

One with just the teams and the actual placing and one for scores with a formula of 10-ABS(predicted - actual) (when you meant lose points you meant if one place out get 9 rather than 10 right?)

TSR Support Team

We have a brilliant team of more than 60 Support Team members looking after discussions on The Student Room, helping to make it a fun, safe and useful place to hang out.

This forum is supported by:
Updated: July 27, 2017
Today on TSR

### Oxbridge

Even more elitist than everyone thought?

### Physically ill after being cheated on

Discussions on TSR

• Latest
• ## See more of what you like on The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

• Poll
Useful resources
Bizarre things students have spent their loans onThings you should budget for at uni

### Need some cash?

How to make money running your own website.

What would an employer find out about you on Google? Find out how to take control.

## Groups associated with this forum:

View associated groups
Discussions on TSR

• Latest
• ## See more of what you like on The Student Room

You can personalise what you see on TSR. Tell us a little about yourself to get started.

• The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.