Rounding up or down to specific decimal digits.
 

Folks,
I am trying to do the following:

I am looking at MSRPs for our products ..for example Product A comes with a
Base price of $71.16..Product B comes with a price of $54.78. I am trying to
round the end number to a 7 or 9...therefore, Prod A becomes $71.17 and
Product B becomes $54.79.....it needs to be somewhat "intuitive" i.e. $54.78
doesn't become $54.77 but rather $54.79 and so on.....

i am thinking I could use an ODD and some variant of ROUND function..not
sure how to combine them for the last decimal place...is this feasible?
Thanks for your help.

Regards,
Shams.

Rounding up or down to specific decimal digits.
 

I hope there is a better way

=A21+(IF(ROUND(MOD(A21,0.1),2)0.07,0.09,0.07)-ROUND(MOD(A21,0.1),2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shams" wrote in message
...
Folks,
I am trying to do the following:

I am looking at MSRPs for our products ..for example Product A comes with
a
Base price of $71.16..Product B comes with a price of $54.78. I am trying
to
round the end number to a 7 or 9...therefore, Prod A becomes $71.17 and
Product B becomes $54.79.....it needs to be somewhat "intuitive" i.e.
$54.78
doesn't become $54.77 but rather $54.79 and so on.....

i am thinking I could use an ODD and some variant of ROUND function..not
sure how to combine them for the last decimal place...is this feasible?
Thanks for your help.

Regards,
Shams.

Rounding up or down to specific decimal digits.
 

Bob,
Thank you very much for your input. This rather complicated formula works
brilliantly! I just have to spend some time to understand what it's doing
....need to understand this intuitively

"Bob Phillips" wrote:

I hope there is a better way

=A21+(IF(ROUND(MOD(A21,0.1),2)0.07,0.09,0.07)-ROUND(MOD(A21,0.1),2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Shams" wrote in message
...
Folks,
I am trying to do the following:

I am looking at MSRPs for our products ..for example Product A comes with
a
Base price of $71.16..Product B comes with a price of $54.78. I am trying
to
round the end number to a 7 or 9...therefore, Prod A becomes $71.17 and
Product B becomes $54.79.....it needs to be somewhat "intuitive" i.e.
$54.78
doesn't become $54.77 but rather $54.79 and so on.....

i am thinking I could use an ODD and some variant of ROUND function..not
sure how to combine them for the last decimal place...is this feasible?
Thanks for your help.

Regards,
Shams.