rounding to nearest 5 or 0?

[Hide]

I'm looking to round up whole numbers to the nearest 5 or
0.

example:
1) 5487 would round to 5490
2) 5481 would round to 5485

How can I do this in Excel?
Thanks,
-hide

 

[Biff]

Hi Norman,

This was a classic case of the poster asking for one thing
than changing it to another!

The subject line: rounding to nearest 5 or 0?
is changed to: I'm looking to round up whole numbers.

No problem however! You covered both requests!

Here's one I was tinkering with a few days ago. Round
*any* number To the nearest multiple of nth. For example,
round to the nearest multiple of 16:

32267 = 32264
32238 = 32248
32201 = 32196
32208 = 32216

Multiple really isn't the correct term. Perhaps round to
the nearest power of 16?

Biff

 

[Norman Harker]

Hi Biff!

I didn't notice until I saw JE's reply. Then I thought, "That's not
like JE! And it wasn't!"

Your conundrum! Closest I can get is:

=INT(A1/100)*100+ROUND(MOD(A1,100)/16,0)*16

With those examples it appeared that you want the last two digits to
be to the nearest 16.

32267 = 32264
I get 322634 Yessss!

32238 = 32248
I get 32232 Bu**er! It is the nearest multiple of 16 of the last two
digits and I suspect your question example

32201 = 32196
I get 32200 Double bu**er! And now I really don't know what your
rounding algorithm is! After all 00 is capable of division by 0.

32208 = 32216
I get 32216

So I get 2/4 but am confused on the algorithm. So I'll need a closer
definition or a brain transplant. Take your pick. I believe there's
quite a lot of politicians who haven't used theirs for years.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Saturday: Bosnia-Herzegovina (Petrovdan); Canada,
Northern Ireland, and Eire (Orangemen's Day); Kiribati (Independence
Day); Malaysia (Birthday of the Yang di-Pertua Negi Pulau Pinang);
Micronesia (Micronesian Day); Mongolia (National Day); Sao Tome &
Principe (Independence Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Biff]

BRAVO!

I'll give you 4/4.

32238 = 32248 - you're suspicion was correct, should have
been 32232

Now that I think about it, 00 would be a logical multiple.
I was stuck on how to handle the mod.

Thanks!
Biff