More function than FLOOR() !

[wilchong via OfficeKB.com]

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

 

[Bob Phillips]

If you won't have very small increments

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

 

[Infinitogool]

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

Regards,
Pedro J.

 

[joeu2004]

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

 

[Ron Rosenfeld]

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

 

[joeu2004]

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.

 

[Ron Rosenfeld]

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

 

[joeu2004]

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)

 

[joeu2004]

On second thought....

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)

Note that that has the same restriction as my original solution,
namely: abs(A1) >= 1. But I don't like discontinuities when we talk
about problems involving negative as well as positive values. So the
following might be better:

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

The same idea could be applied to the OP's problem, if the OP does not
like the restriction of A1 >= 1, to wit:

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

I just wanted to KISS for the OP, especially noting that this is a
"newusers" group (although that often means little), as long as the
suggestion meets the OP's needs as I interpret them.

 

[Ron Rosenfeld]

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

I'm understanding less and less of what you are posting.

Rounding 11 to 10 is what I would call rounding down, not up.

The OP wrote (in part)

10 to 0
11 to 10


Left unsaid is what should happen to a non-integer such as 10.9

Since 10.9 is greater than 10, I would have thought it should be rounded down
to 10.

Your formula would round it down to 0.

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)

This is more in accord with *my* interpretation of what the OP requested.
Thanks.
--ron

 

[joeu2004]

Left unsaid is what should happen to a non-integer such as 10.9
Since 10.9 is greater than 10, I would have thought it should be
rounded down to 10.

Your formula would round it down to 0.

And my point was: left unsaid, there is no reason to think I was
wrong.

I interpreted the OP's intention to be: everything below 11 should
round down to 0, everything below 21 should round down to 10, etc.
Since it was left unsaid, I see no reason to think your interpretation
is any more or less reasonable than mine.

I'm understanding less and less of what you are posting.

Sorry about that. Once you start inventing conditions that are "left
unsaid", there are no limits to what your imagination might conjure
up. I wanted to cover all bases, just for KICKS ("keep it
complicated ...", the opposite of KISS ;->).

That does have the disadvantage of obsuring the KISS solution, which
what I tried to present in the first place.


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

 

[Peo Sjoblom]

Left unsaid is what should happen to a non-integer such as 10.9
Since 10.9 is greater than 10, I would have thought it should be
rounded down to 10.

Your formula would round it down to 0.

And my point was: left unsaid, there is no reason to think I was
wrong.

I interpreted the OP's intention to be: everything below 11 should
round down to 0, everything below 21 should round down to 10, etc.
Since it was left unsaid, I see no reason to think your interpretation
is any more or less reasonable than mine.

How about a vote? I would definitely go for Ron's interpretation,
to me it would be more logical that everything greater than 10 less than or
equal to 20
would be rounded down to 10. Of course the OP also said:

"What I want is to convert 10 to 0, 20
to 10, 30 to 20, 40 to 30"

I think it is more likely that means up to and include 10 to 0 but greater
than 10
will be 10. Of course Bob's formula would do that as well besides working
for less than 1 as long it is greater than 0.


--


Regards,


Peo Sjoblom

 

[Ron Rosenfeld]

And my point was: left unsaid, there is no reason to think I was
wrong.

I interpreted the OP's intention to be: everything below 11 should
round down to 0, everything below 21 should round down to 10, etc.
Since it was left unsaid, I see no reason to think your interpretation
is any more or less reasonable than mine.

I don't disagree with that statement of yours.
--ron