Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get Excel 2003 to automatically round up the result of a
I have a cell, which is the result of one cell minus another, but when the
result is something point 5, excel rounds this down, and I want it to round the result up. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get Excel 2003 to automatically round up the result of a
"dartanion" wrote in message
... I have a cell, which is the result of one cell minus another, but when the result is something point 5, excel rounds this down, and I want it to round the result up. Any ideas? Excel will automatically round "something point 5" up. I suspect the result of your calculation is actually "something point 499999...", which correctly is rounded down. For more help you need to be specific about the the actual data in your cells (as seen in the formula bar, NOT what is displayed), what your formula is, how the cell is formatted, etc. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get Excel 2003 to automatically round up the result of a
I don't know whether you are looking at the rounding when you are displaying
a format with zero decimal places, or whether you are using the ROUND function, but in either case 0.5 *will* round up to 1. If, of course, your number is 0.46, for example, and it had originally been displayed to 1 decimal place it would *look like* 0.5, but it would round down. I would therefore recommend that you display your original number to more decimal places to check exactly what it is. There is a slight possibility that you've fallen victim to the fact that most decimal numbers cannnot be represented exactly in fixed point binary. 0.5 can, but 0.1 can't. [You can't represent 1/10 exactly in fixed point binary, just as you can't represent 1/3 exactly in fixed point decimal.] You may therefore have small rounding errors on your original numbers and thus ended up after your subtraction with something which isn't exactly 0.5 although you would expect that it would be. Again if you extend the number of decimal places to see all 15 significant figures you should see if this is the case. -- David Biddulph "dartanion" wrote in message ... I have a cell, which is the result of one cell minus another, but when the result is something point 5, excel rounds this down, and I want it to round the result up. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get Excel 2003 to automatically round up the result o
Thanks David, dut it isn't working as you suggest. Yes the display is to zero
decimal places. I have expanded the cells to 15 decimal places, and they are as follows A is 79 exactly, B is 16.500000000000000, and this displays as 17, which is as you suggest, but the next cell, C, has the formula =A-B, and the answer it displays is 63. The answer I expect is 62, as when this is printed, everyone tells me my math is wrong! "David Biddulph" wrote: I don't know whether you are looking at the rounding when you are displaying a format with zero decimal places, or whether you are using the ROUND function, but in either case 0.5 *will* round up to 1. If, of course, your number is 0.46, for example, and it had originally been displayed to 1 decimal place it would *look like* 0.5, but it would round down. I would therefore recommend that you display your original number to more decimal places to check exactly what it is. There is a slight possibility that you've fallen victim to the fact that most decimal numbers cannnot be represented exactly in fixed point binary. 0.5 can, but 0.1 can't. [You can't represent 1/10 exactly in fixed point binary, just as you can't represent 1/3 exactly in fixed point decimal.] You may therefore have small rounding errors on your original numbers and thus ended up after your subtraction with something which isn't exactly 0.5 although you would expect that it would be. Again if you extend the number of decimal places to see all 15 significant figures you should see if this is the case. -- David Biddulph "dartanion" wrote in message ... I have a cell, which is the result of one cell minus another, but when the result is something point 5, excel rounds this down, and I want it to round the result up. Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get Excel 2003 to automatically round up the result o
79 - 16.5 is 62.5. This rounds up to 63, so you are getting the correct
answer. You need to remember that formatting the 16.5 to *display* to zero decimal places will change only the display, not the stored number. If you want to round the 16.5 to 17 *before* you do the subtraction, then you either need to use the ROUND function such as =A1-ROUND(B1,0), or you can use Tools/ Options/ Calculation: Precision as Displayed, but I wouldn't recommend the latter as it may well give you unexpected results elsewhere if you don't think carefully about what it is doing. -- David Biddulph "dartanion" wrote in message ... Thanks David, dut it isn't working as you suggest. Yes the display is to zero decimal places. I have expanded the cells to 15 decimal places, and they are as follows A is 79 exactly, B is 16.500000000000000, and this displays as 17, which is as you suggest, but the next cell, C, has the formula =A-B, and the answer it displays is 63. The answer I expect is 62, as when this is printed, everyone tells me my math is wrong! "David Biddulph" wrote: I don't know whether you are looking at the rounding when you are displaying a format with zero decimal places, or whether you are using the ROUND function, but in either case 0.5 *will* round up to 1. If, of course, your number is 0.46, for example, and it had originally been displayed to 1 decimal place it would *look like* 0.5, but it would round down. I would therefore recommend that you display your original number to more decimal places to check exactly what it is. There is a slight possibility that you've fallen victim to the fact that most decimal numbers cannnot be represented exactly in fixed point binary. 0.5 can, but 0.1 can't. [You can't represent 1/10 exactly in fixed point binary, just as you can't represent 1/3 exactly in fixed point decimal.] You may therefore have small rounding errors on your original numbers and thus ended up after your subtraction with something which isn't exactly 0.5 although you would expect that it would be. Again if you extend the number of decimal places to see all 15 significant figures you should see if this is the case. -- David Biddulph "dartanion" wrote in message ... I have a cell, which is the result of one cell minus another, but when the result is something point 5, excel rounds this down, and I want it to round the result up. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to round down the result of a formula | Excel Worksheet Functions | |||
how do I round up a result of a formula in Excel | Excel Worksheet Functions | |||
How do I round up the result of a sum | Excel Worksheet Functions | |||
In Excel, how do you get it to not automatically round my dollars. | Excel Worksheet Functions | |||
how do I get excel 2003 to round up | Excel Worksheet Functions |