Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to round negative and positive numbers
I need to round numbers to the nearest ZERO or FIVE. Both Positive and
Negative. I am using the formula "=ROUND(Q89,1-LEN(INT(Q89))". it does it mostly fine with postive number except 25 gets rouned to 30.. any way of keeping 25 at 25.. and also negative numbers don't work. the cell is blank. not even an error. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to round negative and positive numbers
You didn't state it has to be rounded up or down, with your formula, 16.2558 gets rounded to 20.. I am not sure if that is what you want. Try this.. =IF(Q890, mround(Q89, 5), mround(Q89*-1, 5)*-1) If you want all number to round up, change mround to ceiling -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=558490 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to round negative and positive numbers
knobz wrote: I need to round numbers to the nearest ZERO or FIVE. Both Positive and Negative. I am using the formula "=ROUND(Q89,1-LEN(INT(Q89))". it does it mostly fine with postive number except 25 gets rouned to 30.. any way of keeping 25 at 25.. and also negative numbers don't work. the cell is blank. not even an error. Hi knobz If you use Bearacade's functions you may need to add the Analysis ToolPak: Tools Add-Ins check the Analysis ToolPak checkbox Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to round negative and positive numbers
Bearacade wrote:
=IF(Q890, mround(Q89, 5), mround(Q89*-1, 5)*-1) Why Q89*-1 when -Q89 would seem to do just as well? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to round negative and positive numbers
Yes, you can. I actually didn't know that, thanks =) -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=558490 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to round negative and positive numbers
I wrote:
Bearacade wrote: =IF(Q890, mround(Q89, 5), mround(Q89*-1, 5)*-1) Why Q89*-1 when -Q89 would seem to do just as well? I did not pay close enough attention to that odd formulation. The simplest form of the 3rd parameter is, of course: -mround(-Q89,5). In any case.... The OP wrote: I need to round numbers to the nearest ZERO or FIVE. Both Positive and Negative. I am using the formula "=ROUND(Q89,1-LEN(INT(Q89))". Bearacade paid attention to the OP's first sentence. Perhaps we should pay more attention to the OP's second sentence, which contradicts the first (klunk!). The OP's formulation rounds the __first_significant_integer_digit__ of a positive number. So perhaps the OP wants to round the first significant integer digit to 0 or 5. On the other hand, since that makes little sense, perhaps the OP is using the entirely wrong formula out of ignorance of what it really does, and Bearacade's interpretation might be right after all. On the "third hand", the OP might have really intended something else altogether. Suffice it to say, the OP is likely to get a "correct" answer if he/she explains his/her intent better, with concrete examples that demonstrate the desired behavior for some "interesting" cases. PS: The problem with rounding negative numbers is: there is no universal agreement on what the result should be. Should -1.5 round to -2 or -1? That is a rhetorical question. People have been debating the question at least since Fortran introduced the INT function, as I recall. But since there is no single answer, it would behoove the OP to indicate what he/she wants for specific examples of negative numbers. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to round negative and positive numbers
I wrote:
Bearacade wrote: =IF(Q890, mround(Q89, 5), mround(Q89*-1, 5)*-1) Why Q89*-1 when -Q89 would seem to do just as well? I did not pay close enough attention to that odd formulation. The simplest form of the 3rd parameter is, of course: -mround(-Q89,5). In any case.... The OP wrote: I need to round numbers to the nearest ZERO or FIVE. Both Positive and Negative. I am using the formula "=ROUND(Q89,1-LEN(INT(Q89))". Bearacade paid attention to the OP's first sentence. Perhaps we should pay more attention to the OP's second sentence, which contradicts the first (klunk!). The OP's formulation rounds the __first_significant_integer_digit__ of a positive number. So perhaps the OP wants to round the first significant integer digit to 0 or 5. On the other hand, since that makes little sense, perhaps the OP is using the entirely wrong formula out of ignorance of what it really does, and Bearacade's interpretation might be right after all. On the "third hand", the OP might have really intended something else altogether. Suffice it to say, the OP is likely to get a "correct" answer if he/she explains his/her intent better, with concrete examples that demonstrate the desired behavior for some "interesting" cases. PS: The problem with rounding negative numbers is: there is no universal agreement on what the result should be. Should -1.5 round to -2 or -1? That is a rhetorical question. People have been debating the question at least since Fortran introduced the INT function, as I recall. But since there is no single answer, it would behoove the OP to indicate what he/she wants for specific examples of negative numbers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
Add sequences of positive then negative numbers | Excel Discussion (Misc queries) | |||
Compare Negative & Positive Numbers | New Users to Excel | |||
how do I rank negative and positive numbers? | Excel Worksheet Functions | |||
How can I change positive numbers to negative, i.e. change 50 to - | Excel Discussion (Misc queries) |