This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

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
- Mod formula assistance

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
1738
14

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

May 14, 2020 02:47 AM

I am trying to understand a why a simple MOD function isn’t returning the correct answer.

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

( I can grant you access so you can see the formulas)

On the table titled “SEASON STATS OVERVIEW”, in the OVER field, I am trying to build a larger formula and part of it requires me to use the MOD function. The formula in the OVERS field is **MOD({BALLS BOWLED},6)** but for some reason the record for player 2 is returning a value of 6 when it should be 0. I have tested the MOD function for the number 30 as a number i.e. not generated from a roll up function and that works. But for some reason when the number 30 is generated from a Roll up formula is returns an incorrect value.

I can grant creator access to you if you need to see the formula fields/

Any ideas/assistance, all gratefully received.

Thanks in advance

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

May 14, 2020 08:22 AM

I copied the base and am playing in that copy, so you can remove my access to your original.

After messing with it a bit, I don’t think it’s a problem with MOD, but rather a side-product of some of the math taking place leading up to that, and how those values are being represented internally, regardless of how they appear based on the field formatting options.

First off, using `SUM(values)`

as your aggregation function should return a number, so forget what I said above about the array-to-string-to-number conversion process. That’s not necessary.

Here’s one of the primary clues that leads me to think that the internal numbers are not as clean as they appear. I made a test formula to force `{BALLS BOWLED}`

to an integer:

```
INT({BALLS BOWLED})
```

Putting that next to `{BALLS BOWLED}`

, the result for that problem line surprised me:

Because the `INT()`

function “Returns the greatest integer that is less than or equal to the specified value.”, that tells me that the true value of `{BALLS BOWLED}`

isn’t an even 30 like the field displays, but actually 29.??? However, I wasn’t able to see that detail by forcing `{BALLS BOWLED}`

to display as a decimal, even with the highest precision formatting option.

My next test was to try to round the value with the `ROUND()`

function, and see how the rounded value compared to the unrounded one:

```
ROUND({BALLS BOWLED}, 0) = {BALLS BOWLED}
```

Based on the earlier INT() test, the results didn’t totally surprise me, but also showed that other values were also not as clean as they appeared on the surface:

In the end, applying `ROUND()`

in your `{OVERS}`

field did the trick:

```
MOD(ROUND({BALLS BOWLED}, 0), 6)
```

Why isn’t the value cleaner even though you’re using a combination of rounding functions leading up to that? No idea.

Reply

14 Replies 14

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

May 14, 2020 06:32 AM

Is Balls Bowled a number field? What is the value in Balls Bowled?

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

May 14, 2020 06:54 AM

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

May 14, 2020 07:22 AM

Unless your Roll-up field’s aggregation formula is designed a certain way, it will return an array, even if that array is only a single value. You have two options: modify the rollup aggregation formula to force the result to a number, or convert the array to a number in your `{OVERS}`

field. Not knowing your aggregation formula, I’ll go with the second option.

It’s actually going to be a two-step process: array to string, then string to number. To go from an array to a string, concatenate the array with an empty string:

```
{BALLS BOWLED} & ""
```

To turn that into a number, wrap it inside the `VALUE()`

function:

```
VALUE({BALLS BOWLED} & "")
```

Your final formula in `{OVERS}`

then becomes:

`MOD(VALUE({BALLS BOWLED} & ""), 6)`

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

May 14, 2020 07:41 AM

Hi Justin,

Thank you for trying to help me with this. Much appreciated.

Unless I am doing something very wrong, that didn’t seem to work. It might be easier if I grant you access to the base so you can see everything. If you click on this link, I will grant you creator access

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

May 14, 2020 07:49 AM

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

May 14, 2020 07:51 AM

Solved
See Solution in Thread

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

May 14, 2020 08:22 AM

I copied the base and am playing in that copy, so you can remove my access to your original.

After messing with it a bit, I don’t think it’s a problem with MOD, but rather a side-product of some of the math taking place leading up to that, and how those values are being represented internally, regardless of how they appear based on the field formatting options.

First off, using `SUM(values)`

as your aggregation function should return a number, so forget what I said above about the array-to-string-to-number conversion process. That’s not necessary.

Here’s one of the primary clues that leads me to think that the internal numbers are not as clean as they appear. I made a test formula to force `{BALLS BOWLED}`

to an integer:

```
INT({BALLS BOWLED})
```

Putting that next to `{BALLS BOWLED}`

, the result for that problem line surprised me:

Because the `INT()`

function “Returns the greatest integer that is less than or equal to the specified value.”, that tells me that the true value of `{BALLS BOWLED}`

isn’t an even 30 like the field displays, but actually 29.??? However, I wasn’t able to see that detail by forcing `{BALLS BOWLED}`

to display as a decimal, even with the highest precision formatting option.

My next test was to try to round the value with the `ROUND()`

function, and see how the rounded value compared to the unrounded one:

```
ROUND({BALLS BOWLED}, 0) = {BALLS BOWLED}
```

Based on the earlier INT() test, the results didn’t totally surprise me, but also showed that other values were also not as clean as they appeared on the surface:

In the end, applying `ROUND()`

in your `{OVERS}`

field did the trick:

```
MOD(ROUND({BALLS BOWLED}, 0), 6)
```

Why isn’t the value cleaner even though you’re using a combination of rounding functions leading up to that? No idea.

Reply

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

May 14, 2020 08:23 AM

It looks like you will need to round your original value of “Balls Bowled”, because if you look at the new formula that I just created, which is: `{BALLS BOWLED} & ""`

, it results in non-integer values.

So this formula will work for you:

`MOD(ROUND({BALLS BOWLED},0),6)`

Reply

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

May 14, 2020 08:24 AM

Reply

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

May 14, 2020 08:25 AM

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

May 14, 2020 08:29 AM

`SUM(values)`

to return an array. Same thing for `MIN(values)`

and `MAX(values)`

. However, some aggregation functions will still create arrays. In short, it’s a case-by-case situation.

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

May 14, 2020 08:32 AM

this is brilliant. thank you very much indeed.

Reply

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

May 14, 2020 08:33 AM

thank you as well for taking the time to figure out the solution. Again, much appreciated.

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

May 14, 2020 09:52 AM

Great, thank you! :slightly_smiling_face: