Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
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) |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
On second thought....
On Aug 4, 11:05 pm, joeu2004 wrote: 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. On Aug 4, 11:05*pm, joeu2004 wrote: 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) |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
On Mon, 4 Aug 2008 23:05:45 -0700 (PDT), joeu2004 wrote:
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. ;-) 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 |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
On Aug 5, 11:36*am, Ron Rosenfeld wrote:
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 ----- On Mon, 4 Aug 2008 23:05:45 -0700 (PDT), joeu2004 wrote: 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. ;-) 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 |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
"joeu2004" wrote in message
... On Aug 5, 11:36 am, Ron Rosenfeld wrote: 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 |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
More function than FLOOR() !
On Tue, 5 Aug 2008 12:14:09 -0700 (PDT), joeu2004 wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Will FLOOR & CEILING work with variable stratifications? | Excel Worksheet Functions | |||
floor function | Excel Worksheet Functions | |||
is there a way of drawing floor plans within office | New Users to Excel | |||
Something like CEILING or FLOOR | Excel Worksheet Functions | |||
ceiling & floor | New Users to Excel |