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!

 

[RonCo]

That is really nice and clean - you summed up an answer in th
shortest possible equation!

I probably would have spent hours trying to work something like tha
out

Good job
Ro

 

[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.

 

[Alan]

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

Hi Julie,

Try this:

=CEILING(A1+0.05,0.1)-0.05


HTH,

Alan.

 

[Alan]

Try:

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

I'm not sure that works - if you enter 0.959, I think she wants the
result to be 1.05 whereas this returns 0.95 ?

If so, see my other reply:


Apologies if I mis-read.

Alan.

 

[Vasant Nanavati]

You are correct, Alan ... I just assumed that she was entering only two
decimal places. Thanks!