Mod formula assistance

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

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)

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

Access requested. You could also share a copy of the base without needing to grant access. Open the share options, choose “Create a shared link to the whole base,” then choose “Private read-only link”. Others using that link can then make a copy of the base using the “Copy base” option in the upper right corner.

Thank you that’s good to know. I wasn’t aware. I have granted someone else access but they haven’t come back to me yet, so you may want to copy the base to avoid and overwriting

@Justin_Barrett, it looks like the formula worked without needing to go through your workaround of converting the rollup field into a string, and then a value. Does this mean that Airtable now works properly when applying the “MOD” function to rollup fields?

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.

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)

I don’t think it’s a case of it working properly “now” vs some other behavior in the past. My impression is that the output of a Rollup field depends on the aggregation formula used. It wouldn’t make sense for 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.