G
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
Data Output
1.21 1.29
1.05 1.09
20.66 20.69
J
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
9th.
One way:
=CEILING(A1,0.1)-0.01
F
Frank Kabel
Hi
one way:
=ROUNDUP(A1+0.01,1)-0.01
one way:
=ROUNDUP(A1+0.01,1)-0.01
F
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
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
J
JE McGimpsey
on the other hand probably only a theoretical discussion
Hard to know...<g>
G
Guest
They both work grerat..
Thanks
Thanks
Frank Kabel said:
F
Frank Kabel
JE McGimpsey said:
indeed <vbg>
Best Regards
Frank
G
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
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
R
Ron Rosenfeld
On Thu, 7 Apr 2005 09:39:01 -0700, "David S" <David
Perhaps:
=ROUND(A1+0.005,1)-0.01
--ron
Perhaps:
=ROUND(A1+0.005,1)-0.01
--ron
G
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!
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!
P
Peo Sjoblom
=IF(MOD(A1,1)<0.15,FLOOR(A1,1)-0.01,ROUND(A1+0.005,1)-0.01)
S
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
--
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
G
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.
=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.
G
Guest
Your formula produce identical result as mine... thank you now I have to
options
options
P
Peo Sjoblom
If possible avoid using text function when you deal with decimals, it's
better to use MOD than right in this case
better to use MOD than right in this case
G
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!
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!
S
Stephen
Rachael said:
Assuming all your prices are integers:
=IF(MOD(A1,10)=0,A1-1,A1)