Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 25, 2019 03:22 PM
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.
Solved! Go to Solution.
Dec 26, 2019 07:01 AM
Did you consider using ABS () ? It turns negative numbers into absolute (positive) numbers.
Dec 26, 2019 04:16 AM
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?
Dec 26, 2019 04:52 AM
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:
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.
Dec 26, 2019 05:08 AM
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.
Dec 26, 2019 05:38 AM
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.
Dec 26, 2019 07:01 AM
Did you consider using ABS () ? It turns negative numbers into absolute (positive) numbers.
Dec 26, 2019 07:02 AM
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.
Dec 26, 2019 09:40 AM
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.
Dec 26, 2019 09:44 AM
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
)
Dec 26, 2019 11:57 AM
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.