Rounding in increments of 5,000

[AndiH]

Excel 2007 - I need to round down in increments of 5000. This rounded number will be referenced in other formulas. Example: 46,000. I'm looking for the result 45,000. If 39,000 I'm looking for the result of 35,000.

Similarly in a different column I need to round up in increments of 5,000. If 42,000 I would need to round up to 45,000. If 47,000 need to round up to 50,000.

 

[VoG]

Try

=FLOOR(A1,5000)

and

=CEILING(A1,5000)

 

[shg]

If the numbers are always positive, then to round down (toward zero)

=floor(a1, 5000)

To round up (away from zero)

=ceiling(a1, 5000)

 

[AndiH]

Perfect. Thanks to both of you.

 

[MrKowz]

If you want it to round both ways, try:

=IF(ABS(FLOOR(A1,5000)-A1) < ABS(CEILING(A1,5000)-A1),FLOOR(A1,5000),CEILING(A1,5000))

 

[AndiH]

Nice! Thanks

 

[Jonmo1]

If you want it to round both ways, try:

=IF(ABS(FLOOR(A1,5000)-A1) < ABS(CEILING(A1,5000)-A1),FLOOR(A1,5000),CEILING(A1,5000))

Is the purpose of this to behave like standard round, to the nearest 5000?

Try

=ROUND(A1/5000,0)*5000

 

[MrKowz]

Is the purpose of this to behave like standard round, to the nearest 5000?

Try

=ROUND(A1/5000,0)*5000

Well, if you want to get fancy about it.

 

[Jonmo1]

Well, if you want to get fancy about it.

Fancy would be

=MROUND(A1,5000)

But that requires the Analysis toolpack

 

[AndiH]

Thanks. I'm good.