Fixed exponent in scientific notation

**krusader23** · 11-27-2009, 01:18 PM

Hi, I want to display two values with the scientific notation in a way, that both of the values have the same fixed exponent value, i.e.:
x=2345,5
y=0,897
Should be displayed as:
x=2,3455e3
y=0,000897e3

Any suggestions?

**Richard Buttrey** · 11-27-2009, 01:52 PM

Hi,

Just format them using the scientific notation option.

HTH

**shg** · 11-27-2009, 02:10 PM

Welcome to the forum.

Try formatting as 0.0#####,"e3" adapted for your regional settings (note that it includes both a decimal and a comma)

**krusader23** · 11-27-2009, 05:59 PM

Originally Posted by shg

Try formatting as 0.0#####,"e3" adapted for your regional settings (note that it includes both a decimal and a comma)

Let's say the number x=0,0234
When I format the cell using notation 0.0#### the value is of course calculated right,
but when I use something like this: 0.0#####,"e3" the original value in the cell and the processed value aren't equal: 0.0234!=0,0234e3.

This doesn't solve my problem. What I want to do with the two values is that they should be displayed as:
xe(fixed exponent)
ye(fixed exponent),
no matter, what the actual x and y values are. When I format cells using the engineering notation I still get something like this:
x=0,001029628
y=0,00008481
After processing:
x=1,030e-03
y=84,810e-06

I want the numbers marked as red in the above example to be equal, no matter what x and y are.
If somebody has a macro dealing with this I would be glad to see it (I know some programming languages, but not the VBA).

Cheers.

**shg** · 11-27-2009, 08:49 PM

Here's what I get with my suggestion using US regional settings:

      ---A--- ----B-----
  1   42499.5  42.4995e3
  2   16126.6  16.1266e3
  3   5636.81  5.63681e3
  4   2255.41  2.25541e3
  5   564.627 0.564627e3
  6   361.825 0.361825e3
  7   3.99228 0.003992e3
  8   2.96033  0.00296e3
  9   1.79814 0.001798e3

Not what you want?

**krusader23** · 11-27-2009, 09:55 PM

I want this exactly! Thank you!

I guess I've input your formula wrong, because I use european (polish) regional settings.
But even after reading your last post I'm still unable to "format" the formula the right way.
In Europe, we've got a comma and not a dot in the value representation, so I've figured out I have to change that in your script. But I don't know what sing should I use for: 0,00####,"e3".
And another question: does this thing "e3" is something like a formatting flag for a printf() function in c++?

**here to help** · 06-02-2013, 10:11 AM

If you want complete control over the displayed exponent then you might find this useful:

formula:

your data	desired exponent	fixed exponent format
1.00E-08	-5	=TEXT(A1/10^$B$1,"#,0.00###")&"E"&$B$1
1.00E+03		=TEXT(A2/10^$B$1,"#,0.00###")&"E"&$B$1

displayed result:

your data	desired exponent	fixed exponent format
1.00E-08	-5	0.001E-5
1.00E+03		100,000,000.00E-5

The formatted results can be used in calculations without modification.

**t+2** · 10-25-2019, 04:19 AM

Hi all,
6 years later I do have the same problem. I'm using Excel 2016 German language edition and did not succeed with 0.0#####,e3 either.
While playing around with the formula I more or less accidentally found a solultion that worked for me.
Considering that the decimal separator in Europe is the comma and not the point and that in my case I tried to display big numerical values like 1.036.372.818,17 as 1,04E+09 I used 0,00..."E+09"
Also maybe something has changed during time in Excel but for me not # but . worked well.