Standard index form - Generating leading number (before *10^x part

[Neil Goldwasser]

Hi all! Here is a challenge for you...

Does anybody know how to combine worksheet functions to generate the leading
number from the Standard Index Form version of a "normal number"?

For example,...
....if the input was 340000, the output would be 3.4
....if the input was 0.00278, the output would be 2.78

I realise that Excel can display a "normal" number in this "scientific"
format, but it is for a teaching resource that I am building, so that is no
use to me.

Would it use the LEFT or RIGHT functions?

I have at least worked out that, once I have this leading number, in order
to generate the index the formula would be
=LOG10(complete_number/leading_number)
but I am puzzled about how to get the first bit.

Many thanks in advance, Neil Goldwasser

 

[Castell]

Try this, where A1 is where input value is:
=SUBSTITUTE(SUBSTITUTE(A1,0,""),".","")/10

 

[Neil Goldwasser]

Unfortunately this did not work. For example, using 100 as my input, the
output was 0.1, whereas it should have been 1.
Using 125 as my input, the output was 12.5, whereas it should have been 1.25.

Standard index form always has one non-zero digit before the decimal point.

Many thanks for your attempt though. Hopefully we'll get there...

 

[fernando cinquegrani]

Hi all! Here is a challenge for you...

Does anybody know how to combine worksheet functions to generate the
leading
number from the Standard Index Form version of a "normal number"?

For example,...
...if the input was 340000, the output would be 3.4
...if the input was 0.00278, the output would be 2.78

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))
or
=TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000")
.f

 

[Max]

Try this slight revision to Castell's suggestion:
=SUBSTITUTE(SUBSTITUTE(A1,0,""),".","")/10^(LEN(SUBSTITUTE(SUBSTITUTE(A1,0,""),".",""))-1)

 

[Neil Goldwasser]

Thanks everyone! Problem solved... and much appreciated!
Neil Goldwasser

Hi all! Here is a challenge for you...

Does anybody know how to combine worksheet functions to generate the
leading
number from the Standard Index Form version of a "normal number"?

For example,...
...if the input was 340000, the output would be 3.4
...if the input was 0.00278, the output would be 2.78

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))
or
=TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000")
.f

 

[Neil Goldwasser]

PS - The "winner" was.....

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))

This was the only one that took account of ALL decimal places needed, as
well as taking account of zeros sandwiched between non-zero digits and
therefore still needed (i.e. zeros that are NOT at the end of the number).
For example, the leading number for 10203000 should be 1.0203, and this was
the only formula to produce this.

Again, many thanks for all your help!
Neil Goldwasser

Thanks everyone! Problem solved... and much appreciated!
Neil Goldwasser

Hi all! Here is a challenge for you...

Does anybody know how to combine worksheet functions to generate the
leading
number from the Standard Index Form version of a "normal number"?

For example,...
...if the input was 340000, the output would be 3.4
...if the input was 0.00278, the output would be 2.78

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))
or
=TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000")
.f

 

[Castell]

Fair enough. You're right.

PS - The "winner" was.....

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))

This was the only one that took account of ALL decimal places needed, as
well as taking account of zeros sandwiched between non-zero digits and
therefore still needed (i.e. zeros that are NOT at the end of the number).
For example, the leading number for 10203000 should be 1.0203, and this
was
the only formula to produce this.

Again, many thanks for all your help!
Neil Goldwasser

Thanks everyone! Problem solved... and much appreciated!
Neil Goldwasser

"Neil Goldwasser" <[email protected]> ha scritto
nel
messaggio Hi all! Here is a challenge for you...

Does anybody know how to combine worksheet functions to generate the
leading
number from the Standard Index Form version of a "normal number"?

For example,...
...if the input was 340000, the output would be 3.4
...if the input was 0.00278, the output would be 2.78

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))
or
=TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000")
.f

 

[Neil Goldwasser]

Many thanks for your help though, I really appreciate it!
Neil

Fair enough. You're right.

PS - The "winner" was.....

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))

This was the only one that took account of ALL decimal places needed, as
well as taking account of zeros sandwiched between non-zero digits and
therefore still needed (i.e. zeros that are NOT at the end of the number).
For example, the leading number for 10203000 should be 1.0203, and this
was
the only formula to produce this.

Again, many thanks for all your help!
Neil Goldwasser

Thanks everyone! Problem solved... and much appreciated!
Neil Goldwasser



:

"Neil Goldwasser" <[email protected]> ha scritto
nel
messaggio Hi all! Here is a challenge for you...

Does anybody know how to combine worksheet functions to generate the
leading
number from the Standard Index Form version of a "normal number"?

For example,...
...if the input was 340000, the output would be 3.4
...if the input was 0.00278, the output would be 2.78

your number in [A1]
in [B1]
=A1/10^INT(LOG(A1))
or
=TEXT(A1/10^(INT(LOG(A1))),"0.00") & "E" &TEXT(INT(LOG(A1)),"000")
.f