Rounding to the nearest 9th
 

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

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

One way:

=CEILING(A1,0.1)-0.01


In article ,
"Corby" wrote:

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

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Corby" schrieb im Newsbeitrag
...
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

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


--
Regards
Frank Kabel
Frankfurt, Germany
"JE McGimpsey" schrieb im Newsbeitrag
...
note: your examples round to the next higher 0.09, not to the nearest
9th.

One way:

=CEILING(A1,0.1)-0.01


In article ,
"Corby" wrote:

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

In article ,
"Frank Kabel" wrote:
on the other hand probably only a theoretical discussion :-)

Hard to know...<g

They both work grerat..

Thanks

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Corby" schrieb im Newsbeitrag
...
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" schrieb im Newsbeitrag
...
In article ,
"Frank Kabel" wrote:
on the other hand probably only a theoretical discussion :-)

Hard to know...<g

indeed <vbg
Best Regards
Frank

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

"Corby" wrote:

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

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

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

Rounding to the nearest 9th
 

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!

"Ron Rosenfeld" wrote:

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

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

Rounding to the nearest 9th
 

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


--
Regards,

Peo Sjoblom



"mmmbl" wrote in message
...
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!

"Ron Rosenfeld" wrote:

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

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

Rounding to the nearest 9th
 

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


Replace @mailinator.com with @tiscali.co.uk


"mmmbl" wrote in message
...
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!

"Ron Rosenfeld" wrote:

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

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

Rounding to the nearest 9th
 

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.
"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"mmmbl" wrote in message
...
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!

"Ron Rosenfeld" wrote:

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

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

Rounding to the nearest 9th
 

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

"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom



"mmmbl" wrote in message
...
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!

"Ron Rosenfeld" wrote:

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

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

Rounding to the nearest 9th
 

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


--
Regards,

Peo Sjoblom



"mmmbl" wrote in message
...
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.
"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"mmmbl" wrote in message
...
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!

"Ron Rosenfeld" wrote:

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

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

Rounding to the nearest 9th
 

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!

"Corby" wrote:

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

Rounding to the nearest 9th
 

"Rachael" wrote in message
...
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)