Rounding numbers up or down in same formula

T

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

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

Rick Rothstein

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top