|
Number*10 multiplicity
Hello.
I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
=IF(A111,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(I NDIRECT("1:9
")))),A11*10^(LEN(A11)-FIND(".",A11))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and =A11/B11 which is not -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
How about if A11 contains 1.1 ?
-- Gary's Student "Bob Phillips" wrote: =IF(A111,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(I NDIRECT("1:9 ")))),A11*10^(LEN(A11)-FIND(".",A11))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and =A11/B11 which is not -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
I thought about that, but as his spec (seemed to) discount it, I didn't
bother. You're welcome to enlighten me though. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Gary''s Student" wrote in message ... How about if A11 contains 1.1 ? -- Gary's Student "Bob Phillips" wrote: =IF(A111,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(I NDIRECT("1:9 ")))),A11*10^(LEN(A11)-FIND(".",A11))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and =A11/B11 which is not -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
after all, I guess it can only be 1.1 and 1 (which mine gives) or 1 and 1.1
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Gary''s Student" wrote in message ... How about if A11 contains 1.1 ? -- Gary's Student "Bob Phillips" wrote: =IF(A111,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(I NDIRECT("1:9 ")))),A11*10^(LEN(A11)-FIND(".",A11))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and =A11/B11 which is not -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
In VBA, I would just repeatedly divide A11 by 10 until it result was below 1
(counting all the way) Then apply your formula, adjusting C11 by the count. I am not clever enough (or too lazy) to do this without VBA. By the way, the op's question was an excellent one, as was your reply. I might use them as an example this semester. -- Gary's Student "Bob Phillips" wrote: I thought about that, but as his spec (seemed to) discount it, I didn't bother. You're welcome to enlighten me though. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Gary''s Student" wrote in message ... How about if A11 contains 1.1 ? -- Gary's Student "Bob Phillips" wrote: =IF(A111,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(I NDIRECT("1:9 ")))),A11*10^(LEN(A11)-FIND(".",A11))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and =A11/B11 which is not -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
This also seems to work:
=A1*10^(LEN(A1/10^(INT(LOG(A1))+1))-INT(LOG(A1))-3) for a number in A1, to get the other number just divide the original by this result. Perus wrote: Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
and if A1 contains -100 ??
-- Gary's Student "Lori" wrote: This also seems to work: =A1*10^(LEN(A1/10^(INT(LOG(A1))+1))-INT(LOG(A1))-3) for a number in A1, to get the other number just divide the original by this result. Perus wrote: Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
The OP's question indicated that numbers were positive and specified
the multiplicites. You could replace log(...) by log(abs(...)) in the formula do deal with negatives =A1*10^(LEN(ABS(A1)/10^(INT(LOG(ABS(A1)))+1))-INT(LOG(ABS(A1)))-3) and if you wanted also add a condtion for 0. Gary''s Student wrote: and if A1 contains -100 ?? -- Gary's Student "Lori" wrote: This also seems to work: =A1*10^(LEN(A1/10^(INT(LOG(A1))+1))-INT(LOG(A1))-3) for a number in A1, to get the other number just divide the original by this result. Perus wrote: Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
I agree it was an interesting question, one I will also add to my array
formulae course. As to a solution to the 1.1 question, to derive a solution you have to what you are aiming at, so what do you think it should break down to? Are you a full-time teacher/lecture, if so, may I ask where? Regards Bob "Gary''s Student" wrote in message ... In VBA, I would just repeatedly divide A11 by 10 until it result was below 1 (counting all the way) Then apply your formula, adjusting C11 by the count. I am not clever enough (or too lazy) to do this without VBA. By the way, the op's question was an excellent one, as was your reply. I might use them as an example this semester. -- Gary's Student "Bob Phillips" wrote: I thought about that, but as his spec (seemed to) discount it, I didn't bother. You're welcome to enlighten me though. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Gary''s Student" wrote in message ... How about if A11 contains 1.1 ? -- Gary's Student "Bob Phillips" wrote: =IF(A111,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(I NDIRECT("1:9 ")))),A11*10^(LEN(A11)-FIND(".",A11))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. and =A11/B11 which is not -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
Maybe I'm completely off the wall with this and I am not understanding what
is required but won't: =A11/10^(LEN(INT(A11))-2-(A11<1)) and A11/B11 give you what you are looking for? 0,12 gives 12 & 0,01 53000 gives 53 & 1000 1,1 gives 11 & 0,1 -100 gives -10 & 10 12.1 gives 12.1 & 1 (because you want two figures) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
100 isn't right for one.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sandy Mann" wrote in message ... Maybe I'm completely off the wall with this and I am not understanding what is required but won't: =A11/10^(LEN(INT(A11))-2-(A11<1)) and A11/B11 give you what you are looking for? 0,12 gives 12 & 0,01 53000 gives 53 & 1000 1,1 gives 11 & 0,1 -100 gives -10 & 10 12.1 gives 12.1 & 1 (because you want two figures) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
I see your point Sandy. Bob and I assumed that since two sigificant
figures of precision were given in the examples and no decimal places in the answer that this meant the number of digits entered defined the precision (543210 - 54321 & 10). I think this made for a more interesting problem but taken as written your answer seems perfectly adequate. Sandy Mann wrote: Hi Bob, I'm obviously not understanding what is required because I took the OP's from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another to mean that they wanted a two digit number in one cell and a multiple of 10 in the other so that when they were multiplied together gave the original number. For 100 I get 10 & 10 is this not correct? With you formula I get 1 & 100 which does not meet the OP's two digit requirement. -- Puzzled, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... 100 isn't right for one. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sandy Mann" wrote in message ... Maybe I'm completely off the wall with this and I am not understanding what is required but won't: =A11/10^(LEN(INT(A11))-2-(A11<1)) and A11/B11 give you what you are looking for? 0,12 gives 12 & 0,01 53000 gives 53 & 1000 1,1 gives 11 & 0,1 -100 gives -10 & 10 12.1 gives 12.1 & 1 (because you want two figures) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
Number*10 multiplicity
Hi Lori,
I'm replying at work through Google so I can't see recent posts but perhaps the OP will let us know how it should be viewed. Thank you for explaining your take on it - that hadn't occurred to me. Regards, Sandy Lori wrote: I see your point Sandy. Bob and I assumed that since two sigificant figures of precision were given in the examples and no decimal places in the answer that this meant the number of digits entered defined the precision (543210 - 54321 & 10). I think this made for a more interesting problem but taken as written your answer seems perfectly adequate. |
Number*10 multiplicity
Thank-you Lori, that is exactly as I saw it.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Lori" wrote in message oups.com... I see your point Sandy. Bob and I assumed that since two sigificant figures of precision were given in the examples and no decimal places in the answer that this meant the number of digits entered defined the precision (543210 - 54321 & 10). I think this made for a more interesting problem but taken as written your answer seems perfectly adequate. Sandy Mann wrote: Hi Bob, I'm obviously not understanding what is required because I took the OP's from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another to mean that they wanted a two digit number in one cell and a multiple of 10 in the other so that when they were multiplied together gave the original number. For 100 I get 10 & 10 is this not correct? With you formula I get 1 & 100 which does not meet the OP's two digit requirement. -- Puzzled, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Bob Phillips" wrote in message ... 100 isn't right for one. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Sandy Mann" wrote in message ... Maybe I'm completely off the wall with this and I am not understanding what is required but won't: =A11/10^(LEN(INT(A11))-2-(A11<1)) and A11/B11 give you what you are looking for? 0,12 gives 12 & 0,01 53000 gives 53 & 1000 1,1 gives 11 & 0,1 -100 gives -10 & 10 12.1 gives 12.1 & 1 (because you want two figures) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Perus" wrote in message oups.com... Hello. I don't know "math english" well so forgive me mistakes. I've got a question.How should the formula look which will: take number from A2 cell and shows me 2 digit number in one cell and 10 multiplicity in another E.g. 0,12 will be shown as 12 in one cell and 0,01 in another 53000 will be shown as 53 and 1000. 10 multiplicities range is from 0,01 to 1000000000 (10^ -2 to 10^9) Hope you will help me :D Kacper. |
| All times are GMT +1. The time now is 07:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com