I have a problem for an excel formula. I want to convert 1 to 0, 2 to 0,......
..., 10 to 0. And convert 11 to 10, 12 to 10,............, 20 to 10.

I know the formula of FLOOR can have this function, however, it will convert
10 to 10, 20 to 20 which is not I want. What I want is to convert 10 to 0, 20
to 10, 30 to 20, 40 to 30. Therefore, how can I achieve this result by using
Excel Formular?

Many thanks,
Wilchong

--
Message posted via http://www.officekb.com

If you won't have very small increments

=FLOOR(A21-1/10^10,10)

--
__________________________________
HTH

Bob

"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:88277aa4e627b@uwe...
I have a problem for an excel formula. I want to convert 1 to 0, 2 to
0,......
.., 10 to 0. And convert 11 to 10, 12 to 10,............, 20 to 10.

I know the formula of FLOOR can have this function, however, it will
convert
10 to 10, 20 to 20 which is not I want. What I want is to convert 10 to 0,
20
to 10, 30 to 20, 40 to 30. Therefore, how can I achieve this result by
using
Excel Formular?

Many thanks,
Wilchong

--
Message posted via http://www.officekb.com

Hi wilchong
Try
=FLOOR(A21,10)-(A21=FLOOR(A21,10))*10

Regards,
Pedro J.



I have a problem for an excel formula. I want to convert 1 to 0, 2 to 0,......
.., 10 to 0. And convert 11 to 10, 12 to 10,............, 20 to 10.

I know the formula of FLOOR can have this function, however, it will convert
10 to 10, 20 to 20 which is not I want. What I want is to convert 10 to 0, 20
to 10, 30 to 20, 40 to 30. Therefore, how can I achieve this result by using
Excel Formular?

Many thanks,
Wilchong

On Aug 3, 11:43 pm, "wilchong via OfficeKB.com" <u43231@uwe wrote:
I have a problem for an excel formula. I want to convert 1 to 0,
2 to 0,........, 10 to 0. And convert 11 to 10, 12 to 10,............,
20 to 10.

FLOOR(A1 - 1, 10) seems to do what you want, as long as A1 is at least
1.


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

On Aug 3, 11:43*pm, "wilchong via OfficeKB.com" <u43231@uwe wrote:
I have a problem for an excel formula. I want to convert 1 to 0, 2 to 0,.......
.., 10 to 0. *And convert 11 to 10, 12 to 10,............, 20 to 10. *

I know the formula of FLOOR can have this function, however, it will convert
10 to 10, 20 to 20 which is not I want. What I want is to convert 10 to 0, 20
to 10, 30 to 20, 40 to 30. *Therefore, how can I achieve this result by using
Excel Formular?

Many thanks,
Wilchong

--
Message posted viahttp://www.officekb.com

On Mon, 4 Aug 2008 13:29:42 -0700 (PDT), joeu2004 wrote:

FLOOR(A1 - 1, 10) seems to do what you want, as long as A1 is at least
1.

A1 would also have to be an integer, wouldn't it?
--ron

On Aug 4, 3:49*pm, Ron Rosenfeld wrote:
On Mon, 4 Aug 2008 13:29:42 -0700 (PDT), joeu2004 wrote:
FLOOR(A1 - 1, 10) seems to do what you want,
as long as A1 is at least 1.

A1 would also have to be an integer, wouldn't it?

No. You could just try it yourself.

All of the OP's examples were integer. So it is unclear what the OP
wants to do, for example, with 10.99 and if that is even a relevant
question in the context. If it is, I ass-u-me that the OP knows that
FLOOR "rounds down" (truncates), according to the help text.

On Mon, 4 Aug 2008 18:15:11 -0700 (PDT), joeu2004 wrote:

No. You could just try it yourself.

I did.


All of the OP's examples were integer. So it is unclear what the OP
wants to do, for example, with 10.99 and if that is even a relevant
question in the context. If it is, I ass-u-me that the OP knows that
FLOOR "rounds down" (truncates), according to the help text.


Your method converts anything less than 11 to 0; anything less than 21 to 10

I guess I would have "ass"-umed, from the way the question was posed, than
values over 10 should convert to 10.

But I would agree that his description was ambiguous for handling values 10
and <11.
--ron

On Aug 4, 7:17*pm, Ron Rosenfeld wrote:
I guess I would have "ass"-umed, from the way the
question was posed, than values over 10 should convert
to 10.

Hmm, so you think that the OP chose FLOOR, a function that rounds
down, because the OP intended to round up. ;-)

Oh well, it's really not difficult to offer a constructive
improvement.

To do exactly what the OP asked for:

=floor(A1-1, 10)

To do what Ron wants, I think, as well as what the OP asked for:

=floor(roundup(A1,0)-1, 10)

To do the only other contorted interpretation that I can imagine, as
well as what the OP asked for:

=floor(round(A1,0)-1, 10)

And while we're solving problems that the OP never asked about, any
one of those solutions can be further contorted to handle negative as
well as positive numbers. For example,

=sign(A1)*floor(abs(A1)-1,10)