format numbers in a engineering way
Posted by Jeroen on July 26, 2001 3:17 AM
Hi all,
I would like to format numbers in excel in an engineering way, i.e. have an exponent which is a multiple of 3. Like 15e03 or 600.3e06 or 45e-9. Does anybody know how to do this in excell 2000?
Posted by Ivan F Moala on July 26, 2001 3:59 AM
Select Format | Cells | number
then select Scientific
Ivan
Posted by jeroen on July 26, 2001 4:32 AM
I meant changing the exponent in multiples of 3.
Thus not 1.23e4 but 12.3e3.
Jeroen
Posted by Mark W. on July 26, 2001 7:32 AM
Boy, somewhere I remember seeing this, but I can't
remember where (or how)! Maybe we can re-engineer
it. : )
First, I think we'll have to store it as a text
value. I assume that you're gonna want to use
the value in computations, and we'd have to
coerce the value into a number for that purpose.
Right? Also, you gonna have to refresh my memory
on the rules for engineering notation. Can you
give me an algorithm?
Posted by Jeroen on July 27, 2001 3:32 AM
The algorithm:
The exponent is always a multiple of 3, so the figure before the exponent, the matinesse (I believe), is between 0 and 1000. Examples:
1.3e2=>130e0 or even better 130
1.3e4=>13e3
1.3e5=>130e3
1.3e7=>13e6
1.3e-7=>130e-9
1.3e-9=>1.3e-9
Posted by Mark W. on July 27, 2001 11:02 AM
What about this approach?
Using {0.000123456;123456;12345678912;0.000345} as
sample values in cells A1:A3...
1. Enter =TEXT(A1,"0E+0") into cell B1 and Copy
down.
2. Enter =IF(ISNUMBER(FIND("E-",B1)),CEILING(MID(B1,FIND("E-",B1)+2,5),3),FLOOR(MID(B1,FIND("E+",B1)+2,5),3))
into cell C1 and Copy down.
3. Enter =IF(ISNUMBER(FIND("E-",B1)),A1*10^C1&"E"&-C1,A1/10^C1&"E"&+C1)
into cell D1 and Copy down.
4. Format column D with a horizontal alignment of
"Right"
