scientific notation to specific power 2

[rgreen511]

I want to format a column of numbers in scientific notation, but all to the same power of 10.

Example, I want them all to be E-06:

1.20E-06
0.25E-06
2.57E-06
etc.

Excel by default makes them all whatever power naturally fits.

I'd also like to avoid a concatenate function since that gets messy, and implements rounding issues, etc.

 

[Hasit]

Format your cells as:

##0.0E+6

That should do it.

 

[GlennUK]

Hasit,

I only have access to Excel 97 today, and that format gives very misleading results. The exponent looks like a 6 but is in fact not there, so 0.00000025 and 0.25 both show as 250.0E-6



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Hasit]

Bugger.

You are right Glenn. Not sure 97 vs any other version would give you different results. An error in the format.I tested with 4 numbers and they looked correct, but I was expecting a xxxE-06 and when it appeared, I was pretty pleased.

Sorry rgreen11 - back to the drawing board. The questions seems eminently possible to find a solution for......

 

[rgreen511]

I got it to work, but kind of backwards. I was exporting the data from Access anyway, so I changed the Format property of the column to:

0.00"E-06"

I think this worked in Excel too. I've never seen that documented anywhere, but it worked, so I'm happy. It's similar to concatenate, but much cleaner, and allows it to be fixed at two decimals. I multiplied the column by 10^6 to make all the data be E-06, so the results are valid.

 

[GlennUK]

A formula to convert numbers to a text version of the number to E-6 format:

=10^(LOG10(B2)+6)&"E-06"

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

Glad you got something working. Looks like we posted at the same time.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.