Formula for rounding in excel nearest 5

rilly99

I have calculated a list of prices euros & cents using various inputs and formulas etc

I now want to round this column of numbers up the nearest 10 euros (eg 8763.90 to 8770.00)

using excel 2007

Any help appreciated

bhickey

=ROUND(number,-1)+10

rockal

The above formula would round an amount of 8765.93 to 8780.00
A better option is to use
=ROUNDUP(number,-1)

Dulchie

Try =ceiling(number,step) i.e. =ceiling(8763.90,10.00)

if you need to use for a series on values

=ceiling(a1,10.00) and copy the formula down the column.

sample

data result
999 1000
1000 1000
1001 1010
1002 1010
1003 1010
1004 1010
1005 1010
1006 1010
1007 1010
1008 1010
1009 1010
1010 1010
1011 1020
1012 1020
1013 1020

bhickey

rockal wrote: »

The above formula would round an amount of 8765.93 to 8780.00

Doh! Sorry I meant '=ROUND(number,-1)'. Dunno where I got the '+10' from. I never knew there was a ROUNDUP function too. Looks like there a few ways to do the same thing.

rilly99

Cheers eveyone for the very useful formulas