Calculating negative numbers

Omer_Nesher · ‎Dec 25, 2019

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.

Christiaan_Huiz · ‎Dec 26, 2019

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

See Solution in Thread

Bill_French · ‎Dec 26, 2019

Couple things come to mind…

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

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

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 · ‎Dec 26, 2019

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:

you bet on a winning team and a points difference. points difference will always be positive, because you gamble on the winning team.
if you guess right on the winning team, you get ‘1’ point.
if you guess right on a winning team, the second phase is to calculate if you got it right on the points difference
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 · ‎Dec 26, 2019

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 · ‎Dec 26, 2019

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 · ‎Dec 26, 2019

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

Bill_French · ‎Dec 26, 2019

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 · ‎Dec 26, 2019

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.

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 · ‎Dec 26, 2019

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 · ‎Dec 26, 2019

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.