Rounding to the nearest 9th

[Guest]

Does Excel have the abilty to round to the nearest 9th?

Data Output
1.21 1.29
1.05 1.09
20.66 20.69

 

[JE McGimpsey]

note: your examples round to the next higher 0.09, not to the nearest
9th.

One way:

=CEILING(A1,0.1)-0.01

 

[Frank Kabel]

Hi
one way:
=ROUNDUP(A1+0.01,1)-0.01

 

[Frank Kabel]

Hi J.E.
not sure if this returns the desired results for values such as
1.995
as this returns
1.99
on the other hand probably only a theoretical discussion

Just as alternative;
=CEILING(A1+0.01,0.1)-0.01

 

[JE McGimpsey]

on the other hand probably only a theoretical discussion

Hard to know...<g>

 

[Guest]

They both work grerat..

Thanks

Hi
one way:
=ROUNDUP(A1+0.01,1)-0.01

 

[Frank Kabel]

Hard to know...<g>

indeed <vbg>
Best Regards
Frank

 

[Guest]

Want to add to what Corby wrote.
I would like the same thig, but I would like Excel to round up or down.
Example:
1.21 becomes 1.19
1.35 becomes 1.39
1.34 becomes 1.29

Thanks

 

[Ron Rosenfeld]

On Thu, 7 Apr 2005 09:39:01 -0700, "David S" <David

Want to add to what Corby wrote.
I would like the same thig, but I would like Excel to round up or down.
Example:
1.21 becomes 1.19
1.35 becomes 1.39
1.34 becomes 1.29

Perhaps:

=ROUND(A1+0.005,1)-0.01


--ron

 

[Guest]

I was able to put this to good use for our pricing policy but a new policy
has been adopted that .09 are no longer acceptable. If the price comes out to
1.01 to 1.14 it should go $0.99 or $5.01 to $5.14 should be $4.99. Is there a
way to modify the formula to apply this rule?
Thank you!

 

[Peo Sjoblom]

=IF(MOD(A1,1)<0.15,FLOOR(A1,1)-0.01,ROUND(A1+0.005,1)-0.01)

 

[Sandy Mann]

So what should 1.15 or $5.00 round to?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

the 1.15 should be $1.19 and 5.00 should be $4.99. I used
=IF(RIGHT((ROUND(A1+0.005,1)-0.01),2)="09",ROUND(A1+0.005,1)-0.11,ROUND(A1+0.005,1)-0.01)
and it produce the results I wanted for the spreadsheet I was working on.

 

[Guest]

Your formula produce identical result as mine... thank you now I have to
options

 

[Peo Sjoblom]

If possible avoid using text function when you deal with decimals, it's
better to use MOD than right in this case

 

[Guest]

Hi,
I have a variation on this problem. I need to change every price ending in
"0" to one dollar lower. E.g. if it's $100, it needs to display $99. However,
all other prices can stay the same. Any suggestions?

Thanks!

 

[Stephen]

Hi,
I have a variation on this problem. I need to change every price ending in
"0" to one dollar lower. E.g. if it's $100, it needs to display $99.
However,
all other prices can stay the same. Any suggestions?

Thanks!

Assuming all your prices are integers:
=IF(MOD(A1,10)=0,A1-1,A1)