rounding up to a specific number

crestiemummy

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.

David Joyeuse

@crestiemummy

That would be this:

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

or

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

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

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))

or

=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!!