How would I round up in this way?

[Julie P.]

Hi, I would like to devise an Excel formula to do the following:

If a dollar amount ends in $x.x5, then leave it as is.
But if it ends in anything but $x.x5, then round it up to the next highest
$x.x5.

So, for example:

$1.05 stays as is,
but $0.94 becomes $0.95,
and $0.96 becomes $1.05

Is there such a formula?

Thanks for any help!

Julie

 

[Vasant Nanavati]

Try:

=(ROUND((A1-0.01)*10,0)/10)+0.05

 

[Julie P.]

Try:

=(ROUND((A1-0.01)*10,0)/10)+0.05

Wow! Thanks Vasant. It worked perfectly. Could I ask how this formula works?
I'm not sure if I understand the syntax, and I would like to learn this.
Thanks!

 

[Ron Rosenfeld]

Hi, I would like to devise an Excel formula to do the following:

If a dollar amount ends in $x.x5, then leave it as is.
But if it ends in anything but $x.x5, then round it up to the next highest
$x.x5.

So, for example:

$1.05 stays as is,
but $0.94 becomes $0.95,
and $0.96 becomes $1.05

Is there such a formula?

Thanks for any help!

Julie

Nice clear explanation of what you want.


There may be a simpler way, but I think this will work:

=CEILING(A1,0.05)+(CEILING(A1,0.05)*10=INT(CEILING(A1,0.05)*10))*0.05



--ron

 

[Arvi Laanemets]

Hi

=ROUNDUP(A1/5,2)*5

Btw. I think you need analysis toolpack to be installed, to use
ROUNDUP/ROUNDDOWN functions.