Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimals, how?
I need to round up decimals if they are greater than .9 and down otherwise...
ex.. 33.90 should be rounded to 34 33.98 should be rounded to 34 33.20 should be rounded to 33 33.89 should be rounded to 33 How do I make this happen? Thanks in advance... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimals, how?
Try one of this with your value in cell A1
=CEILING(A1-0.899,1) OR =IF(MOD(A1,1)<0.899,ROUNDDOWN(A1,),ROUNDUP(A1,)) If this post helps click Yes --------------- Jacob Skaria "LogiMAX" wrote: I need to round up decimals if they are greater than .9 and down otherwise... ex.. 33.90 should be rounded to 34 33.98 should be rounded to 34 33.20 should be rounded to 33 33.89 should be rounded to 33 How do I make this happen? Thanks in advance... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimals, how?
On Wed, 19 Aug 2009 03:49:01 -0700, LogiMAX
wrote: I need to round up decimals if they are greater than .9 and down otherwise... ex.. 33.90 should be rounded to 34 33.98 should be rounded to 34 33.20 should be rounded to 33 33.89 should be rounded to 33 How do I make this happen? Thanks in advance... From your examples, I am assuming you want to round up if your values are EQUAL TO or greater than 0.9 =ROUND(A1-0.4,0) --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimals, how?
On Wed, 19 Aug 2009 04:05:01 -0700, Jacob Skaria
wrote: Try one of this with your value in cell A1 =CEILING(A1-0.899,1) OR =IF(MOD(A1,1)<0.899,ROUNDDOWN(A1,),ROUNDUP(A1,) ) You should note that your recommendation only works with input up to 2 decimals of precision. For example 33.899 would round to 34 using your method. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimals, how?
=ROUND(A19-0.4,0)
-- David Biddulph LogiMAX wrote: I need to round up decimals if they are greater than .9 and down otherwise... ex.. 33.90 should be rounded to 34 33.98 should be rounded to 34 33.20 should be rounded to 33 33.89 should be rounded to 33 How do I make this happen? Thanks in advance... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimals, how?
Ron and David wrote:
=ROUND(A19-0.4,0) For a numerical solution, I would be inclined to do: =ROUND(ROUND(A19,2)-0.4,0) to minimize surprises in the general case. Ron and David's formula might suffice if the OP's numbers are all constants. And it might suffice even if they are the result of formulas, as long as the order of magnitude is relatively small. But consider this contrived example: A19: =3333.9 - 11*2^-41 That is largest difference (about 5E-12) that displays as 3333.90...0 to 11 decimal places (15 significant digits). We have seen such small numerical aberrations arising from arithmetic formulas. ROUND(A19 - 0.4, 0) results in 3333, whereas ROUND(ROUND(A19,2) - 0.4, 0) results in 3334 as desired. I was unable to find a simple set of arithmetic operations that results in the contrived value in A19. But that should not be misconstrued to mean that there is none. It simply means that I got tired of trying ;). For example, 113333.9 - 110000 comes very close. It differs from the contrived value by only 1 bit (the 2nd least significant bit). But 113333.9 - 110000 displays as 3333.89...9 when formatted to 11 decimal places. So arguably, Ron and David's formula would be sufficient, by coincidence. But I think ROUND(ROUND(A19,2) - 0.4, 0) would work better even in this last example because the OP is likely to display the result with 10 dp or less (in fact, probably 2 dp), in which case it __appears__ to be 3333.90...0, and the OP might expect it to round to 3334. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =ROUND(A19-0.4,0) -- David Biddulph LogiMAX wrote: I need to round up decimals if they are greater than .9 and down otherwise... ex.. 33.90 should be rounded to 34 33.98 should be rounded to 34 33.20 should be rounded to 33 33.89 should be rounded to 33 How do I make this happen? Thanks in advance... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Decimals, how?
On Fri, 21 Aug 2009 03:14:39 -0700, "JoeU2004" wrote:
For a numerical solution, I would be inclined to do: =ROUND(ROUND(A19,2)-0.4,0) to minimize surprises in the general case. Ron and David's formula might suffice if the OP's numbers are all constants. And it might suffice even if they are the result of formulas, as long as the order of magnitude is relatively small. Interesting discussion pointing out, once again, the difficulties of dealing with Excel's precision and binary data storage. Given the OP's *posted level* of precision, Jacob's formula would also work. Mine and David's are OK with a greater range of input values; and Joeu2004's is even better. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimals? | Excel Discussion (Misc queries) | |||
Whole Number and Decimals | Excel Discussion (Misc queries) | |||
no decimals | Excel Discussion (Misc queries) | |||
decimals only. | Excel Discussion (Misc queries) | |||
fractions and decimals! | Excel Worksheet Functions |