 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Number*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. =IF(A11>1,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(INDIRECT("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) > Hello. > I don't know "math english" well so forgive me mistakes. [quoted text clipped - 7 lines] > Hope you will help me :D > Kacper. How about if A11 contains 1.1 ?  SignatureGary's Student > =IF(A11>1,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(INDIRECT("1:9 > ")))),A11*10^(LEN(A11)-FIND(".",A11))) [quoted text clipped - 27 lines] > > Hope you will help me :D > > Kacper. 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) > How about if A11 contains 1.1 ? > -- [quoted text clipped - 31 lines] > > > Hope you will help me :D > > > Kacper. 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. SignatureGary's Student > I thought about that, but as his spec (seemed to) discount it, I didn't > bother. You're welcome to enlighten me though. [quoted text clipped - 44 lines] > > > > Hope you will help me :D > > > > Kacper. 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 > 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. [quoted text clipped - 20 lines] > > > -- > > > Gary's Student =IF(A11>1,A11/10^MAX(IF(MOD(A11,10^ROW(INDIRECT("1:9")))=0,ROW(INDIRECT("1:9 > > > > ")))),A11*10^(LEN(A11)-FIND(".",A11))) > > > > [quoted text clipped - 28 lines] > > > > > Hope you will help me :D > > > > > Kacper. 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) > How about if A11 contains 1.1 ? > -- [quoted text clipped - 31 lines] > > > Hope you will help me :D > > > Kacper. 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. > Hello. > I don't know "math english" well so forgive me mistakes. [quoted text clipped - 7 lines] > Hope you will help me :D > Kacper. and if A1 contains -100 ?? SignatureGary's Student > This also seems to work: > [quoted text clipped - 14 lines] > > Hope you will help me :D > > Kacper. 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. > and if A1 contains -100 ?? > -- [quoted text clipped - 18 lines] > > > Hope you will help me :D > > > Kacper. 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) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > Hello. > I don't know "math english" well so forgive me mistakes. [quoted text clipped - 7 lines] > Hope you will help me :D > Kacper. 100 isn't right for one. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) > Maybe I'm completely off the wall with this and I am not understanding what > is required but won't: [quoted text clipped - 33 lines] > > Hope you will help me :D > > Kacper. 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. SignaturePuzzled, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk > 100 isn't right for one. > [quoted text clipped - 44 lines] >> > Hope you will help me :D >> > Kacper. 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. > Hi Bob, > [quoted text clipped - 68 lines] > >> > Hope you will help me :D > >> > Kacper. 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 > 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. Thank-you Lori, that is exactly as I saw it. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) > 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 [quoted text clipped - 75 lines] > > >> > Hope you will help me :D > > >> > Kacper. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.