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?

--
Nige Danton

On Dec 8, 10:43 pm, Nige Danton wrote:
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).

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

On Dec 9, 4:35 pm, joeu2004 wrote:
On Dec 8, 10:43 pm, Nige Danton wrote:

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.

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

On Dec 9, 6:20 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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.

--
Nige Danton

On Sun, 9 Dec 2007 04:58:18 -0800 (PST), Nige Danton
wrote:

On Dec 9, 6:20 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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

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

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

On Sun, 9 Dec 2007 10:24:49 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

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

On Dec 9, 3:01 am, Nige Danton wrote:
On Dec 9, 4:35 pm, joeu2004 wrote:
=mid(text(A1,"0.00000000000000E+0"),1+(A1<0),1)

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:
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).

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


-----
Endnotes:

[1] Jerry might correct me and say that the "actual" value is (truly):
0.002998999999999999929722882541227590991184115409 85107421875

On Dec 9, 7:46 am, Ron Rosenfeld wrote:
=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.333333333333333314829616256247390992939472198486 328125. 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.

On Sun, 9 Dec 2007 09:10:54 -0800 (PST), joeu2004 wrote:

On Dec 9, 7:46 am, Ron Rosenfeld wrote:
=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.333333333333333314829616256247390992939472198486 328125. 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

On Sun, 9 Dec 2007 09:10:54 -0800 (PST), joeu2004 wrote:

On Dec 9, 7:46 am, Ron Rosenfeld wrote:
=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.333333333333333314829616256247390992939472198486 328125. 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

On Dec 10, 12:59 am, joeu2004 wrote:

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

On Dec 9, 11:21 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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.

--
Nige Danton