Fixed exponent in scientific notation

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

**shg** · 11-28-2009, 12:52 AM

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** · 11-28-2009, 05:34 PM

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** · 11-28-2009, 07:31 PM

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