Rounddown problem...

[nekteo via OfficeKB.com]

Running down 22.2 to 22 by using "=roundown (B2,0)" works just fine for me
untill i want to run 22 to 21. How to do that?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

[Anne Troy]

What does the 21 represent? Are you rounding down to multiples of 7 (7, 14,
21)? Give more examples of what you want to achieve if you don't understand
my question.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"nekteo via OfficeKB.com" <u17073@uwe> wrote in message
news:5a5afa35e918f@uwe...
> Running down 22.2 to 22 by using "=roundown (B2,0)" works just fine for me
> untill i want to run 22 to 21. How to do that?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200601/1

[Roger Govier]

Hi

It depends what you are trying to achieve.
=ROUNDDOWN(B2,0) will always return the same value as
=INTEGER(B2)
=TRUNC(B2)
if you are wanting the next integer below the value in B2 then
=ROUNDDOWN(B2,0)-1
=INTEGER(B2)-1

If it is some multiple of 21, then maybe
=INTEGER(B2/21)*21
=TRUNC(B2/21)*21

--
Regards

Roger Govier


"nekteo via OfficeKB.com" <u17073@uwe> wrote in message
news:5a5afa35e918f@uwe...
> Running down 22.2 to 22 by using "=roundown (B2,0)" works just fine
> for me
> untill i want to run 22 to 21. How to do that?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200601/1

[joeu2004@hotmail.com]

"nekteo via OfficeKB.com" wrote:
> Running down 22.2 to 22 by using "=roundown (B2,0)"
> works just fine for me untill i want to run 22 to 21.
> How to do that?

Are you saying that you want 22+fraction to round down
to 22 __and__ you want 22 (exactly) to round down to 21
-- in the same formula?

More generally, are you saying that you want n+fraction
to round to n and you want n to round to n-1 in the same
formula?

Doesn't make much sense to me; that is, I cannot think of
a problem where that is the correct solution. But if that's
what you want, the following should do the trick:

=IF(INT(A1)=A1, A1-1, ROUNDDOWN(A1,0))

Caveat: What do you want -22 to "round down" to: -21
or -23? The above would go to -23. But -21 is consistent
with ROUNDDOWN(), which rounds "toward zero". If you
want -21, then the following works, giving 21 for A1=22
and -21 for A1=-22:

=IF(INT(A1)=A1, A1 - SIGN(A1), ROUNDDOWN(A1,0))