Posts by Ocraled

    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

    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! :)


    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. :)