formula by default to look at data within protected cell

[raggatip]

Sorry about the title. I know it's vague but I didn't know how to word it.

Please see the example attachment.

Cell I3 shows a number of points 1,3 or 5 according to how close a prediction (D3 and E3) is to the final score (G3 and H3).

I want to protect this entire sheet except for cells A3 and B3 where the user can input his/her own numbers as the game progresses. I have a league table on another worksheet which ranks people according to the number of points they get. It would be fun of they can enter the current score as the game is in progress within cells A3 and B3 to see how this affects their ranking but when the data within these cells is deleted the formula within cell I3 then looks at D3 and E3.

Is there any way i can do this?

Thanks in advance for your help

[DonkeyOte]

Perhaps:

I3:
=LOOKUP(3*(SIGN(IF(COUNT(A3:B3)=2,A3-B3,D3-E3))=SIGN(G3-H3))+SUM(G3=IF(ISNUMBER(A3),A3,D3),H3=IF(ISNUMBER(B3),B3,E3)),{0,1,3,5},{0,1,3,5})

[raggatip]

Fantastic. Thanks so much for your help! Clearly I have a long way to go with Excel!

[raggatip]

Hi all,

I'm reviving this small project of mine and have noticed something which I would like to avoid. I have changed the spreadsheet slightly having swapped the final result and the prediction cells over, see attachment example new.

My request is basically the same as before but when cells g3,h3, j3, k3, m3 and n3 are blank I want O3=0

[DonkeyOte]

Without delving too deeply - perhaps:

Please Login or Register  to view this content.

above assumes that for a points value to appear there must be a valid prediction and either a "current" or "final" score

[raggatip]

And that's without looking too deeply into it!!? I hurt my brain trying to work out how your original formula worked, anyway that seems to work perfectly. Thanks very much!