Complex Excel formula help please???????

Question

I'm running a competition for predicting premiership football scores and points are calculated in Excel.

The rules are you predict the scores beforehand and you get 3 points if you get the score bang on and 1 point if you predict the right result but the actual score is incorrect (ie. you predict 1-1 and the results is 2-2 or you predict 2-1 and the result is 1-0). You get no points if you predict the wrong result.

So B4 and C4 show the scores for home and away teams respectively for game one, D4 and E4 show the scores for game two, etc etc....

Each alternate row under this holds each players' predictions (Row 6 for player 1, Row 8 for player 2 etc....

I need a formula that will examine the players' predictions against the actual score (entered after the games) and total the points earned in a cell at the end of the row.

I really hope that makes sense, and really hope someone can help.

I've worked out a way of doing it that uses 5 hidden rows above each player's predictions to work it all out. Problem is, with 38 sets of games in the season and 30(ish) players, all these formulas make the spreadsheet too large (over 7mb) to easily email to all participants.

=IF((B$4=B10)*(C$4=C10),2,0) *produces 2 points for a correct prediction - plus 1 from either of the three below to make 3 point total*

=IF((B$4<C$4)*(B10<C10),1,0) *produces 1 point for an away win*

=IF((B$4>C$4)*(B10>C10),1,0) *produces 1 point for a home win*

=IF((B$4=C$4)*(B10=C10),1,0) *produces 1 point for a draw*

last row adds up the sums of the 4 rows above.

Thanks for the help (fingers crossed!) :)

Answer 1

Not the answer to the Excel question, but a suggestion for emailing it. Excel spreadsheets compress quite well so you could send it out in a zip format.

Answer 2

A simple solution might be to create a summary sheet in the same workbook that just displays the results of your formulas, rather than having all the formulas themselves.  If you click on the cell (in the Summary sheet) where you want the result to be and press =, then open up your sheet where all the formulas are and click on the cell where the result is, Excel will insert the correct reference for you.  This can then be copied down to other cells as required.

Then, when you want to e-mail a copy to the participants

1) Right-click on the Summary sheet tab

2) Select Move or Copy

3) Under To book, select (new book)

4) Tick the Create a copy box

5) Click OK

6) Click File &gt; Save As and choose the location and a name for the copy

This will create a new worksheet with just your Summary sheet inside which you should have no trouble sending them.