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
- Other questions
- Rounding formula

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

0
10263
6

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 20, 2018 02:13 AM

Hi there

I have this formula below where I need to also round up by 5 or round down by 5 ( e.g. 4556, to 45560 or 2333 to 2335)

({Original Price AED} * (100 - Discount)) / 100

Could anyone please advise how I integrate the round formula within the above formula?

Thanks,

Nathalie

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 21, 2018 05:24 AM

I think there’s a typo in your original example. If by

round up by 5 or round down by 5

you mean round to the nearest multiple of 5, where 4556 would round to 4555, 2333 to 2335, and 2332 to 2330, the formula you want is

```
ROUND(number*2,-1)/2
```

To incorporate that into your current formula, just replace `'number'`

with your formula:

`ROUND((({Original Price AED} * (100 - Discount)) / 100)*2,-1)/2`

Reply

6 Replies 6

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

May 20, 2018 12:39 PM

That formula would be:

ROUND([NUMBER],-1)

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

May 20, 2018 08:47 PM

HI Andre

Many thanks. How can I include that formula within my existing formula?

({Original Price AED} * (100 - Discount)) / 100

Or do I have to create a separate column to do the ROUND formula?

Solved
See Solution in Thread

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

May 21, 2018 05:24 AM

I think there’s a typo in your original example. If by

round up by 5 or round down by 5

you mean round to the nearest multiple of 5, where 4556 would round to 4555, 2333 to 2335, and 2332 to 2330, the formula you want is

```
ROUND(number*2,-1)/2
```

To incorporate that into your current formula, just replace `'number'`

with your formula:

`ROUND((({Original Price AED} * (100 - Discount)) / 100)*2,-1)/2`

Reply

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

May 25, 2018 06:05 AM

I can see *that* it works, but I don’t quite understand *how* it works so I’m having trouble modifying this formula to round to a multiple of 15. Is this possible? I’m pulling the minute from a date_time field and need to round to the nearest quarter hour.

Thanks,

Jamie

Reply

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

May 25, 2018 06:34 AM

Try

```
ROUND(nbr*.67,-1)/.67
```

That *seems* to work, but play with it a bit to make sure.

**Note:** Anything 52.5 minutes or longer is rounded up to 60 minutes — which you probably don’t want. Here are two variations:

`nbr`

is number of minutes, possibly > 60```
IF(
ROUND(ROUND(MOD(nbr,60)*.67,-1)/.67,0)=60,
INT(nbr/60)+1&':00',
INT(nbr/60)&':'&ROUND(ROUND(MOD(nbr,60)*.67,-1)/.67,0)
)
```

`{hr}`

(hours) and `{min}`

(minutes)```
IF(
ROUND(ROUND(min*.67,-1)/.67,0)=60,
hr+1&':00',
hr&':'&ROUND(ROUND(min*.67,-1)/.67,0)
)
```

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

Jul 03, 2024 04:39 PM

Thank you all