How to round negative and positive numbers

[knobz]

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.

 

[Bearacade]

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(Q89>0, mround(Q89, 5), mround(Q89*-1, 5)*-1)

If you want all number to round up, change mround to ceilin

 

[Scoops]

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

 

[joeu2004]

=IF(Q89>0, mround(Q89, 5), mround(Q89*-1, 5)*-1)

Why Q89*-1 when -Q89 would seem to do just as well?

 

[Bearacade]

Yes, you can. I actually didn't know that, thanks =)

 

[joeu2004]

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....

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.

 

[joeu2004]

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....

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.