rounding up to a specific number

Does anyone know if it is possible to round up to a specific number?

I would like to round up a column of numbers to the nearest 9.95. i.e 1256.18 would round up to 1259.95, 862.68 would round up to 869.95 etc.

I'm really hoping somebody can help.

Find more posts tagged with

Formulas

Accepted answers

David Joyeuse

@crestiemummy

That would be this:

=[Total]@row+(9.95-MOD([Total]@row,10))

=Total66 + ( 9.95 - MOD(Total66 , 10 ))

But it's best practice to use @row instead of row number when doing formulas.

All comments

David Joyeuse

Hi @crestiemummy

Let's do some maths :)

The formula you need should be this one:

=[Number to round column]@row+(9.95-MOD([Number to round column]@row,10))

MOD will return the rest of the division of your number by 10, i.e. 6.18 & 2.68 in your example. Which is all you need to round up to 9.95

Hope it helped!

crestiemummy

Thank you David.

So just to clarify, if the number I want to round is in column "total" and row 66, could you type out the exact formula?

Thanks

Tracey

David Joyeuse

@crestiemummy

That would be this:

=[Total]@row+(9.95-MOD([Total]@row,10))

=Total66 + ( 9.95 - MOD(Total66 , 10 ))

But it's best practice to use @row instead of row number when doing formulas.

Paul Newcome

@David Joyeuse Nice use of the MOD function!! 👍️👍️

crestiemummy

@David Joyeuse thank you so much. The first formula didn't work for me but the second one did, so hours of work saved!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.