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)

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)

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)

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)

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)

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)

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.