 # 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

you can do with a nested if and also a vlookup set

but a sample sheet on how its laid out may help here - if you could attach

as there appears to be quite a few elements - and not sure how they are laid out

ETAF

• Re: comparing cells and return number

Hi

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

## Files

• Re: comparing cells and return number

ok so all the player does is put a score for home and visitor

then you base points on the results

quite a lengthy algorithm here

1. If the bet is completely correct (4-3) -> Return 10 (points).
this is a separate rule - if they predict a tie with the correct goals ???
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)

So I think this will work - BUT I HAVE NOT checked for all permutations

=IF(AND(\$F6=\$G6,J6=\$F6,K6=\$G6),20,IF(AND(J6=\$F6,K6=\$G6),10,IF(AND(OR(J6=\$F6,K6=\$G6),OR(AND(\$F6>\$G6,J6>K6),AND(\$F6<\$G6,J6<K6))),4,IF(OR(AND(\$F6>\$G6,J6>K6),AND(\$F6<\$G6,J6<K6)),3,IF(OR(J6=\$F6,K6=\$G6),1,0)))))

see attached and the area highlighted in green has the formula for all your sample results and teams
if you change any cells in F6:G10
and the teams J6:K10, M6:N10 and P6:Q10 will change my results in
L20:R24

see if it works OK

## Files

ETAF

• 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

Ok, lets us know how you get on - I'm UK based timezone - so may not answer till tomorrow - depending on your timezone

ETAF

• 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

i think this meets the additional criteria

=IF(AND(\$F6=\$G6,J6=\$F6,K6=\$G6),20, IF(AND(\$F6=\$G6,J6=K6),15, IF(AND(J6=\$F6,K6=\$G6),10,IF(AND(OR(J6=\$F6,K6=\$G6),OR(AND(\$F6>\$G6,J6>K6),AND(\$F6<\$G6,J6<K6))),4,IF(OR(AND(\$F6>\$G6,J6>K6),AND(\$F6<\$G6,J6<K6)),3,IF(OR(J6=\$F6,K6=\$G6),1,0))))))

see attached - BUT please check out fully before implementation

## Files

ETAF

• 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. ## Files

• Re: comparing cells and return number

= IF( OR(\$F6="",\$G6="",J6="",K6=""),0,
to the first part of the formula and it will need a bracket ) at the end

= IF( OR(\$F6="",\$G6="",J6="",K6=""),0,IF(AND(\$F6=\$G6,J6=\$F6,K6=\$G6),20, IF(AND(\$F6=\$G6,J6=K6),15, IF(AND(J6=\$F6,K6=\$G6),10,IF(AND(OR(J6=\$F6,K6=\$G6),OR(AND(\$F6>\$G6,J6>K6),AND(\$F6<\$G6,J6<K6))),4,IF(OR(AND(\$F6>\$G6,J6>K6),AND(\$F6<\$G6,J6<K6)),3,IF(OR(J6=\$F6,K6=\$G6),1,0)))))))

it will set the points to zero 0
if any one of the cells - team score results or players score prediction is blank

## Files

ETAF

• 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!

• Re: comparing cells and return number

excellent , thanks for taking the time to let me know

ETAF

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!