Number*10 multiplicity

[Perus]

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

 

[Bob Phillips]

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

 

[Gary''s Student]

How about if A11 contains 1.1 ?

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

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.

 

[Lori]

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.

 

[Gary''s Student]

and if A1 contains -100 ??
--
Gary's Student

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.

 

[Lori]

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.

 

[Bob Phillips]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Bob Phillips]

100 isn't right for one.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Sandy Mann]

Hi Bob,

I'm obviously not understanding what is required because I took the OP's

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Lori]

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]

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

 

[Bob Phillips]

Thank-you Lori, that is exactly as I saw it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)