Rounding numbers up or down in same formula

[Tired]

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)

 

[JE McGimpsey]

Assuming your values are integers, one way:

=CEILING(A1 - 2.0000000001, 5)

 

[Tired]

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.

 

[Rick Rothstein]

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

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

 

[joeu2004]

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

 

[JE McGimpsey]

Ooops, I meant to say

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

 

[joeu2004]

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.