Grooming the last digit of a number...

[teebruin]

I've been playing around with Ceiling, Floor, and MRound, but I don't think these will work. I have a column full of prices that end in all different digits, because they are the result of formulas. Now, I would like to groom every number to end in the number 9. I.e. if the result comes out to 2.22, I would like to groom that to 2.29. The formulas above use multiples, so I don't think they will work.

I was just wondering if anyone else has needed to groom pricing, and what other formulas may be available to do this.

Thanks

 

[Aladin Akyurek]

On 2002-09-30 17:08, teebruin wrote:
I've been playing around with Ceiling, Floor, and MRound, but I don't think these will work. I have a column full of prices that end in all different digits, because they are the result of formulas. Now, I would like to groom every number to end in the number 9. I.e. if the result comes out to 2.22, I would like to groom that to 2.29. The formulas above use multiples, so I don't think they will work.

I was just wondering if anyone else has needed to groom pricing, and what other formulas may be available to do this.

Thanks

=SUBSTITUTE(A1,RIGHT(A1),9)+0

where A1 houses a number or a formula that returns a numeric result that must be groomed.