Calculating negative numbers

[Omer_Nesher]

Hey,

I’m trying to calculate points difference.

So, if the points difference <= 5 then I’ll give the player 2 ranking points. if the difference is great than that, he doesn’t get any points.

I’ve noticed that Airtable doesn’t treat 5 and -5 the same.
So, I’ve made two fields.

Here’s my formula:

IF(AND({Team Bet}=“Win”), IF(AND({Home Score},{Away Score}), IF({Points Difference Bet}={Actual Points Difference},“3”,IF(OR({Actual Diff - Diff Bet}<=5,{Diff bet - Actual Diff}<=5,“2”,“0”)))))

But, I get nothing.
Can anyone spot my mistake?
Please help.

[Bill_French]

Couple things come to mind…

Omer_Nesher:

IF(AND({Team Bet}=“Win”), IF(…

I think an AND() with one evaluation is redundant. Consider…

IF({Team Bet} = “Win”, IF(...

Omer_Nesher:

IF(AND({Home Score},{Away Score})

Evaluating these fields as booleans might be tripping you up. If {Home Score} == -5 or zero, it will be FALSE. Values greater than zero will resolve to TRUE. Is that what you want your logic to do?

[Omer_Nesher]

Hey Bill,

Thanks for the reply.

I hear what you say about IF(AND being redundant. I’ll look into it.
The reason I did this is to keep the cell blank, cause if it’s blank it defaulted to “Loss”.

Per your second Q, I’m inputting two scores, as you gathered, a home score and away score, one for each team.
The rules are these:

1. you bet on a winning team and a points difference. points difference will always be positive, because you gamble on the winning team.
2. if you guess right on the winning team, you get ‘1’ point.
3. if you guess right on a winning team, the second phase is to calculate if you got it right on the points difference
4. hit on the exact points difference, and get ‘3’ points. if your points difference is 1-5 points away from the actual difference, you get ‘2’ points. otherwise, you get nothing.

This all worked out well. I encountered a problem when a game ended with the score 110:112 and I gambled on a 15 points difference, when it was actually a two points difference,
so, the formula calculated actual points diff (2) - points diff bet (15), which resulted in -13.
The formula still gave me ‘2’ points, when I should have gotten nothing.

[Bill_French]

Omer_Nesher:

The formula still gave me ‘2’ points, when I should have gotten nothing.

So, you can take comfort in knowing that the following part of the formula kicked in; it just didn’t resolve as expected.

IF ( 
  OR (
    {Actual Diff - Diff Bet} <= 5,
    {Diff bet - Actual Diff} <= 5,“2”,“0”
  )
)

Now that it’s formatted better, try this…

IF ( 
  OR (
    {Actual Diff - Diff Bet} <= 5,
    {Diff bet - Actual Diff} <= 5
  ),
  “2”,
  “0”
)

My hunch is you have excluded the last IF()'s outcomes (2 or zero) from the evaluation.

[Omer_Nesher]

Hmm, Still not Budging.
The formula looks like this now:

IF(AND({Team Bet}=“Win”), IF(AND({Home Score},{Away Score}), IF({Points Difference Bet}={Actual Points Difference},“3”,IF(OR({Actual Diff - Diff Bet}<=5, {Diff bet - Actual Diff}<=5),“2”,“0”))))

While fields are:
Score: 110:112
Points Difference Bet: 15
Actual Points Difference: 2
Actual Diff - Diff bet: -13
Diff Bet - Actual Diff: 13
Points Diff Score: 2

Still getting “2” when I shouldn’t have.

[Christiaan_Huiz · Accepted solution]

Did you consider using ABS () ? It turns negative numbers into absolute (positive) numbers.

[Bill_French]

Omer_Nesher:

Still getting “2” when I shouldn’t have.

Your logic suggests otherwise in this example:

The nested IF() is designed to return “2” when:

{Actual Points Difference (2)} - {Points Difference Bet (15)} <= 5 (-13)

That seems to be the right outcome.

[Bill_French]

Christiaan_Huizer:

Did you consider using ABS () ? It turns negative numbers into absolute (positive) numbers.

I don’t think the issue is an inability to calculate the condition, although, his logic might need ABS(). He’s making an evaluation that returns a “2” when he believes it should be zero. But his logic is clear - if the computed value is less than 5, return “2”, and gosh - it does exactly that. :winking_face:

If he has a different logic in mind (and that may be the case), he needs to build that.

Omer_Nesher:

the formula calculated actual points diff (2) - points diff bet (15), which resulted in -13.
The formula still gave me ‘2’ points, when I should have gotten nothing.

As such, I must ask - why does @Omer_Nesher think 2 minus 15 is not less than 5? The code says “return 2” if the product is less than 5.

[Bill_French]

Omer_Nesher:

Still getting “2” when I shouldn’t have.

If this is what you’re expecting, you need to simply reverse the polarity of the OR’d evaluation.

IF({Points Difference Bet} = {Actual Points Difference},
    3,
    IF(
        OR(
            {Actual Points Difference} - {Points Difference Bet} <= 5,
            {Points Difference Bet} - {Actual Points Difference} <= 5
        ),
        0,
        2
    )
)

But stepping back a little, this logic seems flawed - I think you are essentially making it impossible for this to ever evaluate to TRUE.

IF(
    OR(
        {Actual Points Difference} - {Points Difference Bet} <= 5,
        {Points Difference Bet} - {Actual Points Difference} <= 5
    ),
    0,
    2
  )

[Omer_Nesher]

Thanks Christiaan!

So simple, yet, so smart.
I was not aware of the ABS function. That solved it.

I really appreciate everyone’s effort and attention.

Thank you all! what a great community.