Multiple IF/THEN formula help

[Grizz]

Hi

I am trying to create a formula which will work out a score based on a player's prediction of a football match.

There are some conditions in working out the score given...3 points for a correct score and 1 point for the correct team winning. If the prediction is for a draw and the result is a draw then again 3 points for a correct score and 1 point if the game is a draw with a different score.

I have created some other simple IF formulas before but this one has got me baffled, I'm unsure of how to nest so many conditions into a formula or whether it's better to have it as VBA code which again I have minimal experience with

I have attached a file showing the layout and a summary of what I'm trying to achieve. I would be extremely grateful for any help on this. I have spent quite a while trying to figure this out already and not come up with anything that works

Grizz (very much a noob excel user)

[ConneXionLost]

Hi Grizz,

This should do for Alan's score:

Please Login or Register  to view this content.

just copy and paste the cell for Bob & Dave.

Cheers,

[JBeaucaire]

Put this in J4 then copy it over to M4 and P4:

=((H4>I4)=($D4>$E4)) + ((($D4=H4)*($E4=I4))*2)

[DonkeyOte]

For testing result types I find SIGN a very useful function

J4: =(SIGN(H4-I4)=SIGN($D4-$E4))+2*AND($D4=H4,$E4=I4)

JB, I think yours would fail if the result was a win for 2nd team and prediction was draw (eg 1-3, 2-2) because the initial test would return TRUE in both instances.

[ConneXionLost]

It seems Grizz must also decide what to award the individual that chooses one team to win, but the game ends as a draw.

Cheers,

[DonkeyOte]

I think it is clearly 0 given result & score must both be incorrect.

[JBeaucaire]

Good catch, Don, a replacement non-IF formula:

=(AND(ABS(H4-I4)=ABS($D4-$E4), (H4>=I4)=($D4>=$E4))) + ((($D4=H4)*($E4=I4))*2)

[Grizz]

Thanks to you all..it works great now. Can't thank you all enough

I have added to all of your reputations, and put the thread as SOLVED

Thanks yet again

[Grizz]

I have adapted ConneXionLost's formula to my real file and it works great unless there is no score entered in the E and F columns and someone has predicted a draw. As you can see from the attached file it awards 3 points because the E and F columns are equal, with nothing entered...

I wonder whether it is possible to put another condition on the formula that it returns a value of zero or leaves the cell blank if there is no score entered in the E and F columns?

Thanks again

(Not sure whether I should remove the SOLVED prefix?)

[Grizz]

I have partially solved the latest problem with an ISBLANK statement but it seems I need another one somewhere as it still puts the result of 3 if there are no predictions entered as well. I have tried another ISBLANK statement in various places but can't seem to find the right position in the formula without getting an error..

Thanks in advance for any advice

[ConneXionLost]

Hi Grizz,,

From your latest sheet in cell K4 change the formula to:

Please Login or Register  to view this content.

and apply as before.

Cheers,

[Grizz]

Hi ConneXionLost,

That works perfectly now...thank you so much

Grizz.