Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
In article ,
"Frank Kabel" wrote: on the other hand probably only a theoretical discussion :-) Hard to know...<g |
#5
|
|||
|
|||
"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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding numbers up or down | Excel Discussion (Misc queries) | |||
Rounding in Trendline Equation | Charts and Charting in Excel | |||
Rounding to thousands | Excel Discussion (Misc queries) | |||
How do I make Excel stop rounding off my numbers that are 16 digi. | Excel Discussion (Misc queries) |