Rounddown function

[Project_NP_LS]

Hello Airtable community.

I am trying to move my excel spreadsheets across to Airtable and would welcome any help with the Rounddown function. In Excel, it looks like this **=(ROUNDDOWN(C5,0)6)+((C5-(ROUNDDOWN(C5,0)))10)

For anyone who is a cricket fan, I am looking to convert overs which consist of a max of 6 balls into total number of balls bowled. So for example, 8.4 overs = 52 balls (8 overs of 6 balls each = 48 + the 0.4 which is actually 4 balls, therefore 52.

The excel formula above works perfectly but I am struggling to convert it into an Airtable formula.

I have attached an image for ease of reference.

Any help welcome.

Best

Ladi

[kuovonne]

Take a look at the INT and FLOOR functions.

(INT({OVERS}) * 6) + (({OVERS} - INT({OVERS})) * 10)

[Project_NP_LS]

Thank you very much. Since sending the message, I worked it out with this formula (ROUNDDOWN(OVERS,0)*6)+(OVERS-ROUNDDOWN(OVERS,0))*10. The formula also works without the first and last brackets of (ROUNDDOWN(OVERS,0) *6)

Thanks again for your input and for the “Formula field reference” link. That’s very helpful

[Project_NP_LS]

**I have used the following formula in the ‘Balls Bowled’ column and it works well (ROUNDDOWN(OVERS,0)6)+(OVERS-ROUNDDOWN(OVERS,0))10.

**I then created a ‘Testing Column’ using the IF BLANK function IF(OVERS=BLANK(),"",(ROUNDDOWN(OVERS,0)6)+(OVERS-ROUNDDOWN(OVERS,0))10).

What I can’t work out is why some of the results in the Testing column don’t return integers.

Any ideas?

[kuovonne]

Project_NP_LS:

Decimals are not stored as exact values. Round OVERS to the tenth’s place before you subtract the whole number part.

(ROUNDDOWN(OVERS,1)-ROUNDDOWN(OVERS,0))

When a formula field returns a number, you can format how many decimal places to show. That makes it hard to diagnose place value issues for decimals.

[Project_NP_LS]

IF(OVERS=BLANK(),0,(ROUNDDOWN(OVERS,0)*6)+(OVERS-ROUNDDOWN(OVERS,0))*10)

This seems to work now (no idea why). I played around by breaking the formula down and when replace the “” with a 0 i.e. IF(OVERS=BLANK(),0, instead of IF(OVERS=BLANK(),"", it prefers that for some bizarre reason.

Odd

[Project_NP_LS]

also you if you replace “” with BLANK(), it recognises that and actually leaves the cell blank. So in short, it doesn’t like the use of “”

[kuovonne]

When you had "" in the formula, the formula could potentially result in a value that was a text string, thus the field would not give you number formatting options, and decimals would be shown with lots of decimal places.

When you replace "" with BLANK() the formula no longer has the possibility of returning a string, so number formatting rules that limit the number of decimal places apply.

[Project_NP_LS]

That makes perfect sense. Thanks. I am just adjusting from excel to Airtable, so just learning to think about things slightly differently.

On a separate note, could you offer any guidance with regards to the following 2 Queries.

Query 1 - Duration
How do I format Duration to only display in secs i.e. 3.15 secs as opposed to 0:03.15? (see attached screenshot)

Query 2 - Alignment
How do I align my columns i.e. to the left, right or centre?

[kuovonne]

Formatting Duration

The duration field does not have an option to display only seconds. If you want to display only seconds without the leading zeros, you can use a formula field to display the duration field, and set the number of decimal places you want in the formatting tab when you create/customize the field.

Or you can simply use a number field instead of a duration field.

Alignment

As far as I know, you cannot manually set the alignment of fields. Number fields are aligned right. Text fields are aligned left. Text fields that contain numbers are aligned left.