format cells number to 3 significant figures??

[wb198]

Hi,

Is it possible to format cells so that Excel shows the data to 3 significant
figures? (without having to introduce a formula in the cell)
the best I can get is using my custom format
# ##0.###

which isn't exaclty what I'm looking for, and is also annoying because it
always shows the ".", so "100" always displays as "100."

 

[FSt1]

hi
what's wrong with formatting it to Number with 3 decimals places?
or did i miss somthing?

regards
FSt1

 

[David Biddulph]

The OP didn't want 3 decimal places (123.456), he wanted 3 significant
figures (0.456 or 123 or 3.46).

 

[wb198]

Sorry, I should have explained.
"3 significant figures" means that whatever the number,
4 567 000
567.9
2.4678
0.235
only the first 3 significant digits are rounded and displayed:
4 570 000
568
2.47
0.235

I don't want to have to put a ROUND formula in every cell.
I just thought that Excel might have a tool for doing this without having to
put in a formula.

If this isn't possible, maybe someone knows a format that will at least
allow me to control the decimals (limit to 2 or 3 decimal places), and not
put a "." if the number has no decimals.

 

[Bernd P]

Hello,

You can take the number format
##0E+0
but it is the scientific format...

Regards,
Bernd

 

[joeu2004]

If this isn't possible, maybe someone knows a format that will
at least allow me to control the decimals (limit to 2 or 3 decimal
places), and not put a  "." if the number has no decimals.

Scientific format with 2 decimal places is the only way I know to
ensure that only the first 3 significant digits are displayed. But I
presume that is not acceptable, especially considering your last
comment.

You might process the Scientific-formatted number. For example:

=left(text(A1,"0.00E+00"),4)*10^right(text(A1,"0.00E+00"),3)

Using the General format, that seems to get what you want for numbers
with no more than 8 significant digits to the left of the decimal
place or 6 significant digits to the right of the place. That is,
numbers between 0.000100 and 99949999. Some numbers less than 0.0001
will work if they have less than 3 significant decimal digits (e.g.
0.000009, but not 0.0000091).

PS: Of course, it would be more efficient if you put =TEXT(A1,"0.00E
+00") into a helper cell, which you can hide.

 

[joeu2004]

Errata...

=left(text(A1,"0.00E+00"),4)*10^right(text(A1,"0.00E+00"),3)

Using the General format, that seems to get what you want for
numbers with no more than 8 significant digits to the left of the
decimal place or 6 significant digits to the right of the place.

Probably more correct for me to say "no more than 8 digits to the
left" or "6 digits to the right".

Also, the LEFT(...,4) expression works only for non-negative values in
A1. To accomodate negative values, change that expression to:

left(text(A1,"0.00E+00",4+(A1<0))

 

[Bernd P]

Hello,

IF using a formula, why not
=--TEXT(A1,"##0E+0")
formatted General?

Regards,
Bernd

 

[joeu2004]

IF using a formula, why not
=--TEXT(A1,"##0E+0")
formatted General?

Does that work for you? If so, what version of Excel are you using?
Using Office Excel 2003, that does not meet the OP's specifications,
if I understand you correctly. I put that formula into B1, formatted
General. In A1, I try the following values:

A1: 4567000
OP wants: 4570000
My formula: 4570000
Your formula: 5000000

A1: 2.4678
OP wants: 2.47
My formula: 2.47
Your formula: 2

A1: 12.35
My formula: 12.4
Your formula: 12

A1: 0.001235
My formula: 0.00124
Your formula: 0.001

Note: Your formula does work the OP's examples of 567.9 and 0.235.

 

[Bernd P]

Hello,

A dot (comma) did not show up, sorry:
=--TEXT(A1,"0.00E+0")

Regards,
Bernd

 

[joeu2004]

=--TEXT(A1,"0.00E+0")

Yes. Getting your point moments after pressing Submit and leaving for
the gym, I realized that would work. Klunk! But arguably, my
original solution is better for anyone who gets paid by the character
<g>.

 

[David Biddulph]

No, I think you'll need a ROUND formula, such as =ROUND(A2,2-INT(LOG10(A2)))

 

[Ron Rosenfeld]

Hi,

Is it possible to format cells so that Excel shows the data to 3 significant
figures? (without having to introduce a formula in the cell)
the best I can get is using my custom format
# ##0.###

which isn't exaclty what I'm looking for, and is also annoying because it
always shows the ".", so "100" always displays as "100."

In Excel, "format" usually refers to changing the display without changing the
underlying value.

That being the case, I don't believe you can, other than by using the
Scientific format as pointed out by others.


--ron

 

[andy]

Sorry, I should have explained.
"3 significant figures" means that whatever the number,
4 567 000
567.9
2.4678
0.235
only the first 3 significant digits are rounded and displayed:
4 570 000
568
2.47
0.235

I don't want to have to put a ROUND formula in every cell.
I just thought that Excel might have a tool for doing this without having to
put in a formula.

If this isn't possible, maybe someone knows a format that will at least
allow me to control the decimals (limit to 2 or 3 decimal places), and not
put a  "." if the number has no decimals.





- Show quoted text -

Yes, you CAN do this. It is done with the Thousands separator in
custom number formats

Display a thousands separator

To display a comma as a thousands separator or to scale a number by a
multiple of 1,000, include a comma in the number format. To
display As Use this code
12000 12,000 #,###
12000 12 #,
12200000 12.2 0.0,,

 

[Ron Rosenfeld]

Yes, you CAN do this. It is done with the Thousands separator in
custom number formats

Display a thousands separator

To display a comma as a thousands separator or to scale a number by a
multiple of 1,000, include a comma in the number format. To
display As Use this code
12000 12,000 #,###
12000 12 #,
12200000 12.2 0.0,,

The OP gave some sample's and how he would like them displayed.

How would you devise a format that will meet his requirements, and not have to
be individualized for each entry?

It can be done fairly easily with VBA, but I don't see how your advice applies,
without manually changing the format for each entry variation.

Here are the OP's samples:

Sample #'s Desired display
4567000 4570000
569.9 568
2.4678 2.47
0.235 0.235

--ron