Turn on suggestions

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

Showing results for

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: 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

Solved

Jump to Solution

1
3317
0

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.