Fixed exponent in scientific notation

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?

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.

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++?

Hi,
I've found and answer.
You have to type in: 0,0### "e3"
The space character right before the first double quote means: "display the value as thousands (divide the value by 1000)" and the "e3" thing is just a double char string added at the end of the number.
The thing with the space character is that when you add this space character at the end of the formula (and only at the end of the formula) Excel will treat it as a conversion flag: "display as thousands". Double space = "display as millions" and so on.

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.