round to nearest 50

[oldham94l]

im using ROUND(E7,-2) to round to the nearest hundred, but is there a way to adapt it to round to the nearest 50, or 75 ?

 

[Mark W.]

=ROUND(E7/50,0)*50

 

[IML]

you could use
=ROUND(E7/50,0)*50

or the somewhat finicky
=MROUND(E7,50)
which requires the analyst tool pak be installed.

 

[oldham94l]

thanks, is there a way i can get it to allways round up? not down

 

[IML]

=CEILING(E7,50)

whose opposite is
=floor(E7,50)

 

[oldham94l]

, one more question, if i enter 0 in E7 i need it to be ignored and give a result of 0,

but if someone enters 1 in E7 then it will round it to 50

is this possible?

 

[plettieri]

Will you want to consider negative numbers?
pll

 

[oldham94l]

no, only greater than 0

 

[plettieri]

Doesn't the =CEILING(E7,50) give you the desired result?

pll

 

[Mark W.]

On 2002-10-15 16:08, oldham94l wrote:
, one more question, if i enter 0 in E7 i need it to be ignored and give a result of 0,

but if someone enters 1 in E7 then it will round it to 50

is this possible?

Have you not tried =CEILING(E7,50)?