Rounding up to nearest 500

[Guest]

Call me crazy, but I have read every rounding question and answer and I
cannot get Excel to round up to the nearest 500.

IE: 525446 needs to be 525500

Can anyone help! Heather

 

[David Biddulph]

Yes, I'll call you crazy!

=CEILING(A1,500)

 

[joeu2004]

=ROUNDUP(A1,-2)

That rounds up to the nearest __100__, not nearest 500 except by
coincidence. It works with the OP's example (525446). But
roundup(525321,-2) is 525300, not 525500.

That might, indeed, what the OP meant. But....

Call me crazy, but I have read every rounding question and answer and I
cannot get Excel to round up to the nearest 500.
IE: 525446 needs to be 525500

Are you sure that is what you really want? Be sure to test any
solution offered with a variety of numbers. For example:

=500*roundup(525446/500, 0)

yields 525500, as you wish. But:

=500*roundup(525501/500, 0)

yields 526000. That is what "roundup to the nearest 500" means.

 

[David Biddulph]

That rounds up to the nearest __100__, not nearest 500 except by
coincidence. It works with the OP's example (525446).

But roundup(525321,-2) is 525300, not 525500.

It actually gives 525400, but we know what you mean.

 

[Guest]

You can do that without ABS.....

=CEILING(A1,SIGN(A1)*500)

 

[bplumhoff]

Hello,

=ROUNDUP(A1*2,-3)/2

Regards,
Bernd