ROUNDUP to nearest 5th except 10th

[davidlim]

hi,

i know this is a strange request, but i'm looking for a formula to roundup any number to nearest 5th, but not 10th.

example.

137, roundup to 145 (excluded 140)
318, roundup to 325 (excluded 320)
220, roundup to 225

tried ROUNDUP and MROUND, either formula will gives me nearest 10th as well.

thanks

 

[sambit]

Hi,
Assume your data in cell A1
Try:
=MROUND(A1,10)+5

 

[Chihiro]

sambit, I think that will roundup 5 to 15.

If 5 should remain as 5...
=IF(RIGHT(A1,1)="5",A1,MROUND(A1,10)+5)
should work.

 

[davidlim]

thanks sambit and chihiro, both solutions works great!