Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Calculating negative numbers

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
4115
9

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 26, 2019 07:01 AM

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

Reply

9 Replies 9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 26, 2019 07:01 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
)
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.