Football Prediction Spreadsheet

[mixbits]

I have attached a football prediction spreadsheet called Empty Master Forecast.
You get 7 Points for an exact Score either Win or Draw
You get 3 points for a Winning prediction or a non exact score draw
Zero for an incorrect prediction.
The predictions are made by each entrant and submitted into the spreadsheet at the start of the season
The scores are submitted after each game has been played in the Results table.
The predictions are held in Forecast 1 and Forecast 2

In forecast 2 I have test results input to get the formula to give me the correct result into cell AC3
This should give a result which is subsequently summed into cell AD3

However, I cannot the formula to compute correctly and seek help and assistance.

I would be grateful if some learned friend could review the spreadssheet and refine the formula for me

Many thanks for your help

Michael

[cronos]

Hi, I made a small change to the pred res formula, basically I gave it a value of 3 when the Derby loses

original in Y3 in forecast2
=IF(X3>W3,2,IF(X3=W3,1,0))

edited
=IF(X3>W3,2,IF(X3=W3,1,3))


similarly using the same logic, I did the same to the Results page, cell G25. Did you intend to give it a 0 value if the Derby loses?
original
=IF(ISBLANK(E25),0,IF(F25>E25,2,IF(F25=E25,1,IF(F25=-1,-1,0))))

edited
=IF(ISBLANK(E25),0,IF(F25>E25,2,IF(F25=E25,1,3)))


that was preparation to get the main formula in forecast2 AC3 going, I added logic for checking both cells (W3 & X3) for emptiness
original
=IF(ISBLANK(W3),0,IF(Y3=Z3,IF(W3=AA3,IF(X3=AB3,7,0),3),0))

edited
=IF(OR(ISBLANK(W3),ISBLANK(X3)),0,IF(Y3=Z3,IF(AND(W3=AA3,X3=AB3),7,3),0))


hope it helps

[mixbits]

Thank you for your reply,
I will try this out as soon as possible
and let you know how I get on

I did intend that for any loss from either team would result in a zero score and no points would be added.

kind regards

michael

[mixbits]

Hi Cronos,
I have included the formula into the spreadsheet and it was successful thanks to you.

I am extremely grateful for your help, I guess old age is catching up with me as I could not see where
I was going wrong although I created 95% of the spreadsaheet it was the last 5% that giving me problems

Once again many thanks

Michael

[cronos]

you're welcome, glad to help...you were almost there