comparing cells and return number

Hello,

I have absolutely no experience in using Excel functions or macros so though I might find some help from here.

Thing is, I have an excel sheet for a private competition in sports games.
How can I do a function, that automatically calcutes points for every "bet" against the game result?

For example:

If game finishes 4-3 I'd like to compare it for every players guessed score and give points according to this:

1. If the bet is completely correct (4-3) -> Return 10 (points). In case of tie (like 3-3, -> Return 20 (points)
2. If the bet has a correct winner, with correct goals for eather team (4-x / x-3) -> Return 4 (points)
3. If the bet has a correct winner (home/visitor) -> Return 3 (points)
4. If the bet has incorrect winner but amount of goals for eather team correct -> Return 1 (point)
5. If the bet is completely wrong -> Return 0 (points)

At least for me, this sounds more like rocket science but just wanted to check if this is anyway doable.

Re: comparing cells and return number

Hi

Thanks for reply.

Here's the idea in a small scale example (Points for players added manually).

Re: comparing cells and return number

Wow, thank a lot!

I'll check that out when I get home from work, but a quick look already shows that this is exactly what I am looking for.

Re: comparing cells and return number

I'm from Finland, so 2 hours ahead of you.

I made some testing and it sure is working the way I requested.

One thing came into my mind though, concerning games that will end up as a tie.
As for now, the function works like it should when the tie is fully correct regarding numbers (like 2-2 will give that 20 points.)
I was wondering weather it would be possible to adjust this a bit further? For example, if the result is 2-2, but someone has guessed 1-1 or 3-3 or what ever tie with incorrect numbers, now they will receive 0 points.
In those cases, I think it would be fair to receive 15 points for correct tie but incorrect numbers.

But anyway, I don't know how to thank you already.
Your function will save a lot of manual work in the future!

Re: comparing cells and return number

That works just perfectly, except for two things.

When I enter scores to player cells, in case of a tie, the formula gives 15 points, even when there is no game result yet to compare it to.
All other scores (home win / visitor win), return 0 points as they should and change to correct points after adding the final result.

The other one is a minor and completely tolerable thing of Points cell showing 20 points, in situation where there is no score data added yet to players score cells (= empty cells return 20 points, instead of 0.)
This is not a big deal, but could result some confusions, since I am counting total points / day, then next days points are added to previous etc.

Here's the whole "beta testing" sheet attached.

Huge thanks for your help already!

Re: comparing cells and return number

A huge thanks to you once more.

I'll fiddle with that during the weekend.

Re: comparing cells and return number

Works like a charm!

I owe you a pint or few.
:cheers:

Let the games begin!