# Solved: Excel 2003 Round to nearest ten

Discussion in 'Business Applications' started by holymoly, Mar 27, 2008.

Not open for further replies.

Joined:
Jun 21, 2003
Messages:
635
for example a number like 253,372

returns 253,370

or a number 68,538

returns 68,540

I've tried
=Roundup(number,-1)
but the first number 253,372
returens
253,380 and not 253,370

2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
=round(a1/10,0)*10

Joined:
Jun 21, 2003
Messages:
635
I found =MROUND(A1,10) you may have to do a Tools->Cells->Add-in-Analysis Tools VBA install

So if A1 is 22 you get 20

or if A1 is 28 you get 30

=========================================

Bomb#21 also works

only problem with both is 25 or 35 returns 20 and 30

and not 30 and 40

4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Hmm. My Analysis Toolpak seems to have gone AWOL.

So, that's solved then?

5. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Mine doesn't for me. Check your typing.

Joined:
Jun 21, 2003
Messages:
635
Well an interesting observation. some numbers ending in 5 go up to nearest 10 and some down.

ie. 25 can returns 30

but 35 returns 30

bizarre

7. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
For me, 25 > 30; 35 > 40.

Joined:
Jun 21, 2003
Messages:
635
Ok try this number

86,345

using yours I get 86,340

but 311,245

i get 311,250

9. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
I get 86350.

Joined:
Jun 21, 2003
Messages:
635
I think I know why.

My A1 number is a formula(calculation). If I change A1 to raw data or 86,345 i get 86,350

But then with a formula calculation of 311,245 as A1 I do get 311,250 and not 311,240

Joined:
Jul 1, 2005
Messages:
8,546
Doh!

12. ### MRdNk

Joined:
Apr 7, 2007
Messages:
439
Your raw data is probably 86,344.9 or lower, but only showing anything after the decimal place, so rounding down is actually correct.

Joined:
Jun 21, 2003
Messages:
635
check here

#### Attached Files:

• ###### Book3.xls
File size:
18 KB
Views:
121

Joined:
Jun 21, 2003
Messages:
635

yes that's it

15. ### MRdNk

Joined:
Apr 7, 2007
Messages:
439
Yep:
C1 = 86,344.9
C5 = 311,245.4
Hence the error.

As Seen On