Rounding numbers up or down in same formula
 

I am trying to create a single formula in one cell that will result in the
following outcomes:

A value ending in 0 through 2 needs to round down to 0 (e.g., $25,402 =
$25,400).

A value ending in 3 through 5, needs to round up to the nearest 5 (e.g.,
$24,174 = $24,175).

A value ending in 6 through 7, needs to round down to the nearest 5 (e.g.,
$18,217 - $18,215).

A value ending in 8 or 9, needs to round up the nearest 10 (e.g., $14,408 =
$14410)

Rounding numbers up or down in same formula
 

Assuming your values are integers, one way:

=CEILING(A1 - 2.0000000001, 5)



In article ,
Tired wrote:

A value ending in 0 through 2 needs to round down to 0 (e.g., $25,402 =
$25,400).

A value ending in 3 through 5, needs to round up to the nearest 5 (e.g.,
$24,174 = $24,175).

A value ending in 6 through 7, needs to round down to the nearest 5 (e.g.,
$18,217 - $18,215).

A value ending in 8 or 9, needs to round up the nearest 10 (e.g., $14,408 =
$14410)

Rounding numbers up or down in same formula
 

I was hoping to figure out how to set up parameters to either round up or
round down in the same formula depending on the data.

However, individually implementing CEILING and FLOOR has worked as an
interim measure.

Thanks.

"JE McGimpsey" wrote:

Assuming your values are integers, one way:

=CEILING(A1 - 2.0000000001, 5)



In article ,
Tired wrote:

A value ending in 0 through 2 needs to round down to 0 (e.g., $25,402 =
$25,400).

A value ending in 3 through 5, needs to round up to the nearest 5 (e.g.,
$24,174 = $24,175).

A value ending in 6 through 7, needs to round down to the nearest 5 (e.g.,
$18,217 - $18,215).

A value ending in 8 or 9, needs to round up the nearest 10 (e.g., $14,408 =
$14410)

Rounding numbers up or down in same formula
 

You might consider this formula (it appears to work with both positive and
negative numbers)...

=5 * INT((A1 + 2.5) / 5)

--
Rick (MVP - Excel)


"Tired" wrote in message
...
I was hoping to figure out how to set up parameters to either round up or
round down in the same formula depending on the data.

However, individually implementing CEILING and FLOOR has worked as an
interim measure.

Thanks.

"JE McGimpsey" wrote:

Assuming your values are integers, one way:

=CEILING(A1 - 2.0000000001, 5)



In article ,
Tired wrote:

A value ending in 0 through 2 needs to round down to 0 (e.g., $25,402 =
$25,400).

A value ending in 3 through 5, needs to round up to the nearest 5
(e.g.,
$24,174 = $24,175).

A value ending in 6 through 7, needs to round down to the nearest 5
(e.g.,
$18,217 - $18,215).

A value ending in 8 or 9, needs to round up the nearest 10 (e.g.,
$14,408 =
$14410)

Rounding numbers up or down in same formula
 

On Nov 15, 4:43 pm, JE McGimpsey wrote:
Assuming your values are integers, one way:
=CEILING(A1 - 2.0000000001, 5)

Given your assumption, why not simply:

=ceiling(A1 - 2, 5)

In both cases (yours and mine), this assumes A1 = 2.

But I would write CEILING(INT(A1)-2,5) to avoid the assumption.


----- original posting -----

On Nov 15, 4:43*pm, JE McGimpsey wrote:
Assuming your values are integers, one way:

* *=CEILING(A1 - 2.0000000001, 5)

In article ,



*Tired wrote:
A value ending in 0 through 2 needs to round down to 0 (e.g., $25,402 =
$25,400).

A value ending in 3 through 5, needs to round up to the nearest 5 (e.g.,
$24,174 = $24,175).

A value ending in 6 through 7, needs to round down to the nearest 5 (e.g.,
$18,217 - $18,215).

A value ending in 8 or 9, needs to round up the nearest 10 (e.g., $14,408 =
$14410)

Rounding numbers up or down in same formula
 

Ooops, I meant to say

Assuming your values *aren't* all integers...

In article
,
joeu2004 wrote:

On Nov 15, 4:43 pm, JE McGimpsey wrote:
Assuming your values are integers, one way:
=CEILING(A1 - 2.0000000001, 5)

Given your assumption, why not simply:

=ceiling(A1 - 2, 5)

In both cases (yours and mine), this assumes A1 = 2.

But I would write CEILING(INT(A1)-2,5) to avoid the assumption.


----- original posting -----

On Nov 15, 4:43*pm, JE McGimpsey wrote:
Assuming your values are integers, one way:

* *=CEILING(A1 - 2.0000000001, 5)

In article ,



*Tired wrote:
A value ending in 0 through 2 needs to round down to 0 (e.g., $25,402 =
$25,400).

A value ending in 3 through 5, needs to round up to the nearest 5 (e.g.,
$24,174 = $24,175).

A value ending in 6 through 7, needs to round down to the nearest 5
(e.g.,
$18,217 - $18,215).

A value ending in 8 or 9, needs to round up the nearest 10 (e.g., $14,408
=
$14410)

Rounding numbers up or down in same formula
 

On Nov 15, 9:14*pm, JE McGimpsey wrote:
Ooops, I meant to say
* *Assuming your values *aren't* all integers...

And I shoulda written:

=ceiling(round(A1,0)-2,5)

The point is: oh so many people (sigh) see the numbers formatted with
zero dp and think that is the value of the cell, but often it is not.
Based on that assumption, even if the OP's numbers are not integral
values, he probably wants them to be. But he probably wants WYSIWYG
-- hence the ROUND(,0). Just a guess, of course.