Fixed exponent in scientific notation

[krusader23]

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]

Hi,

Just format them using the scientific notation option.

HTH

[shg]

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]

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]

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

Please Login or Register  to view this content.

Not what you want?

[krusader23]

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

[shg]

I don't know what the analog is in C++, and I can't tell you how to change the format string for Polish settings, sorry.

Perhaps someone else will pipe in.

[krusader23]

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.

[Richard Buttrey]

Hi,

To meet your original request that the numbers should display the same fixed exponent, then the numbers should look like the following

2.3455E+3
897.0-3

You can get these by formatting the numbers with

##0.###0E+0

You'll need to replace the "." ,which in my regional settings is the decimal point, with your local setting - presumably the ","

Rgds

[here to help]

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]

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.