First significant figure

[Nige Danton]

I have some data with a large range of values (e.g. 2.25E+23 to
1.65E-41 and some with negative values) and I want to extract the
first significant figure.

For all values larger than 1, =LEFT() does the job nicely but fails
when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.

How do I extract the first significant figure, regardless of the size
of the number or it's sign?

Many thanks for any help

[1] Presumably thats a consequence of the manner excel uses to stores
the decimals?

 

[joeu2004]

How do I extract the first significant figure, regardless of the size
of the number or it's sign?

One way perhaps:

=mid(text(A1,"0.00000000000000E+0"),1+(A1<0),1)

That is 14 zeros after the decimal point, the maximum displayable
precision. That should guard against rounding errors that might
change the appearance of the significant digit.

However, it is not clear to me what you are considering to be the
"first significant" digit. It was not clear to me what problem you
encountered with numbers "less than about 1.00E-19". If you want the
result to be the first significant digit displayed in a cell, I think
you would need the TEXT() format to match the numeric format of the
cell.

Here are some numbers to test with in addition to random numbers:

=-2*(2^1023 - 2^970) [smallest neg number; farthest from zero]
=-(2^-1022) [largest neg number; closest to zero]
0
=2^-1022 [smallest pos number]
=2*(2^1023 - 2^970) [largest pos number]

Note: If 2*(2^1023 - 2^970) does not work on your CPU or version of
Excel, you can get pretty close with simply 2*(2^1023 - 2^971).

 

[Rick Rothstein \(MVP - VB\)]

For all values larger than 1, =LEFT() does the job nicely but fails

when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.

You should be able to take care of the negative values by using the ABS
function before using the LEFT function. For example,

=LEFT(ABS(A1))

However, I do not understand what problem you are having with values "larger
than 0 but less than about 1.00E-19"... can you clarify that statement and,
perhaps, give an example or two of what you mean?

Rick

 

[Nige Danton]

One way perhaps:

=mid(text(A1,"0.00000000000000E+0"),1+(A1<0),1)

That is 14 zeros after the decimal point, the maximum displayable
precision. That should guard against rounding errors that might
change the appearance of the significant digit.

Thanks for your help, I'll try your suggestion.

However, it is not clear to me what you are considering to be the
"first significant" digit.

If the number is larger than 1, then first digit on the left e.g. 1 is
the first significant figure of 123

If the number is smaller than 1, then the first non zero digit after
the decimal place e.g. 1 is the first significant figure of 0.00123

It was not clear to me what problem you

encountered with numbers "less than about 1.00E-19".

Apologies. I should have said "larger than".

What I mean is this. =LEFT(1E-18) returns 0 whereas =LEFT(1E-19)
returns 1.

I'm guessing this is a consequence of how the numbers are stored...?

If you want the

result to be the first significant digit displayed in a cell, I think
you would need the TEXT() format to match the numeric format of the
cell.

Here are some numbers to test with in addition to random numbers:

=-2*(2^1023 - 2^970) [smallest neg number; farthest from zero]
=-(2^-1022) [largest neg number; closest to zero]
0
=2^-1022 [smallest pos number]
=2*(2^1023 - 2^970) [largest pos number]

Note: If 2*(2^1023 - 2^970) does not work on your CPU or version of
Excel, you can get pretty close with simply 2*(2^1023 - 2^971).

Thank you, I will check out your suggestions.

 

[Nige Danton]

For all values larger than 1, =LEFT() does the job nicely but fails
when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.

You should be able to take care of the negative values by using the ABS
function before using the LEFT function. For example,

=LEFT(ABS(A1))

However, I do not understand what problem you are having with values "larger
than 0 but less than about 1.00E-19"... can you clarify that statement and,
perhaps, give an example or two of what you mean?

Rick

Yes, what I mean is this. =LEFT(1E-18) returns 0 whereas =LEFT(1E-19)
returns 1.

 

[Ron Rosenfeld]

For all values larger than 1, =LEFT() does the job nicely but fails
when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.

You should be able to take care of the negative values by using the ABS
function before using the LEFT function. For example,

=LEFT(ABS(A1))

However, I do not understand what problem you are having with values "larger
than 0 but less than about 1.00E-19"... can you clarify that statement and,
perhaps, give an example or two of what you mean?

Rick

Yes, what I mean is this. =LEFT(1E-18) returns 0 whereas =LEFT(1E-19)
returns 1.

Something like this:

=LEFT(TEXT(A1,"0.0"& REPT("0",50)&"E+0"),1)

You can replace the 50 with some number that is greater than the maximum number
of decimal places you might have in your original number.


--ron

 

[Rick Rothstein \(MVP - VB\)]

For all values larger than 1, =LEFT() does the job nicely but fails

when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.

You should be able to take care of the negative values by using the ABS
function before using the LEFT function. For example,

=LEFT(ABS(A1))

However, I do not understand what problem you are having with values
"larger
than 0 but less than about 1.00E-19"... can you clarify that statement
and,
perhaps, give an example or two of what you mean?

Yes, what I mean is this. =LEFT(1E-18) returns 0 whereas =LEFT(1E-19)
returns 1.

You could always do this...

=LEFT(SUBSTITUTE(SUBSTITUTE(ABS(A6),"0",""),".",""))

Rick

 

[Rick Rothstein \(MVP - VB\)]

For all values larger than 1, =LEFT() does the job nicely but fails

when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.

You should be able to take care of the negative values by using the ABS
function before using the LEFT function. For example,

=LEFT(ABS(A1))

However, I do not understand what problem you are having with values
"larger
than 0 but less than about 1.00E-19"... can you clarify that statement
and,
perhaps, give an example or two of what you mean?

Rick

Yes, what I mean is this. =LEFT(1E-18) returns 0 whereas =LEFT(1E-19)
returns 1.

Something like this:

=LEFT(TEXT(A1,"0.0"& REPT("0",50)&"E+0"),1)

You can replace the 50 with some number that is greater than the maximum
number
of decimal places you might have in your original number.

That will work as long as you put A1 in an ABS function first... the OP said
he had negative values too.

Rick

 

[Ron Rosenfeld]

That will work as long as you put A1 in an ABS function first... the OP said
he had negative values too.

I missed that:

=LEFT(TEXT(ABS(A1),"0.0"& REPT("0",50)&"E+0"),1)


--ron

 

[joeu2004]

Dovetailing Ron's improvement, the simplest expression might be:

=left(text(abs(a1),"0.00000000000000E+0"))

Again, that is 14 zeros after the decimal place. It makes no sense to
have more.

However, it is not clear to me what you are considering to be the
"first significant" digit.

[....]
If the number is smaller than 1, then the first non zero digit after
the decimal place e.g. 1 is the first significant figure of 0.00123

What I meant was: if your expectation of the "significant digit" is
based on the display of a number in a format other than Scientific
notation with 14 decimal places, the result of the LEFT(TEXT(...))
formula above might surprise you.

For example, consider the value 0.002999, which you display in the
format Number with 5 decimal places. It appears to be 0.00300. But
the LEFT(TEXT(...)) formula will return "2" because the actual value
is 2.999E-3 [1].

Would you want the answer to be "3" instead?


I wrote previously:
This is somewhat anal, but the following should work on all
implementations:
2*(2^1023 - 2^971) + 2^971.

 

[joeu2004]

=LEFT(TEXT(ABS(A1),"0.0"& REPT("0",50)&"E+0"),1)

It does not make sense to have more than 14 zeros after the decimal
places in Scientific notation format -- i.e. REPT("0",13) in your
formula. Beyond than, Excel simply displays zeros. For example,
consider the value 1/3. Mathemathically, we know that is a fraction
of repeating 3s. In the 64-bit IEEE 754 binary representation, it is
exactly 0.333333333333333314829616256247390992939472198486328125. But
in Excel Scientific notation format with 18 decimal places, it is
displayed as 3.333333333333330000E-01, not 3.333333333333333148E-01.
Increasing the number of decimal places simply increases the number of
appended zeros.

 

[Ron Rosenfeld]

It does not make sense to have more than 14 zeros after the decimal
places in Scientific notation format -- i.e. REPT("0",13) in your
formula. Beyond than, Excel simply displays zeros. For example,
consider the value 1/3. Mathemathically, we know that is a fraction
of repeating 3s. In the 64-bit IEEE 754 binary representation, it is
exactly 0.333333333333333314829616256247390992939472198486328125. But
in Excel Scientific notation format with 18 decimal places, it is
displayed as 3.333333333333330000E-01, not 3.333333333333333148E-01.
Increasing the number of decimal places simply increases the number of
appended zeros.

Well, that's why I wrote to replace the 50 with the maximum number of decimals
in the number. But you're correct. It would have been more efficient to just
put in a 13.
--ron

 

[Ron Rosenfeld]

It does not make sense to have more than 14 zeros after the decimal
places in Scientific notation format -- i.e. REPT("0",13) in your
formula. Beyond than, Excel simply displays zeros. For example,
consider the value 1/3. Mathemathically, we know that is a fraction
of repeating 3s. In the 64-bit IEEE 754 binary representation, it is
exactly 0.333333333333333314829616256247390992939472198486328125. But
in Excel Scientific notation format with 18 decimal places, it is
displayed as 3.333333333333330000E-01, not 3.333333333333333148E-01.
Increasing the number of decimal places simply increases the number of
appended zeros.

I should have written, "... just put in a 13, as you did in an earlier post
that I missed".


--ron

 

[Nige Danton]

For example, consider the value 0.002999, which you display in the
format Number with 5 decimal places. It appears to be 0.00300. But
the LEFT(TEXT(...)) formula will return "2" because the actual value
is 2.999E-3 [1].

Would you want the answer to be "3" instead?

No, it's the actual value that I want (in this example that's 2)
regardless of how it is displayed.

 

[Nige Danton]

For all values larger than 1, =LEFT() does the job nicely but fails
when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.
You should be able to take care of the negative values by using the ABS
function before using the LEFT function. For example,
=LEFT(ABS(A1))
However, I do not understand what problem you are having with values
"larger
than 0 but less than about 1.00E-19"... can you clarify that statement
and,
perhaps, give an example or two of what you mean?

Yes, what I mean is this. =LEFT(1E-18) returns 0 whereas =LEFT(1E-19)
returns 1.

You could always do this...

=LEFT(SUBSTITUTE(SUBSTITUTE(ABS(A6),"0",""),".",""))

Rick

All three solutions work perfectly. Thanks very much for everybodies
help.