Excel automation: significant digits.

Is there a way to specify the amount of significant digits in an excel cell, using ole automation?
leon321Asked:
Who is Participating?
 
Phoenix_sConnect With a Mentor Commented:
OK... I fed your values into excel. I believe you are using a german version or some other european type... due to the use of commas for decimals

regardless...  the first column is your unformatted numbers, then the second column is the output provided by excel when the cell format is defaulted to 2 decimal places

12345.12345      1.23E+04
1234              1.23E+03
12.3456              1.23E+01
1.6              1.60E+00
0.06666666      6.67E-02
0.06              6.00E-02

perhaps you did something wrong or didn't specify decimal places in your format. but as far as I can see... you have 3 significant figures, and an exponent to deal with.  how about reconsidering my answer again...?
0
 
mhervaisCommented:
when you use DDE, as well as OLE automation, you can call virtually all the functions that you would use manually.

so the way to do it would be :
1) to select your cell
2) issue a format command

in excel you would type this :

Range("F10").Select // your cell
Selection.NumberFormat = "0.00" // for instance the number of digits after the comma.

to know easily what commands to make, create new macros, inspect them, you will get your source code.

regards marc
0
 
leon321Author Commented:
I'm sorry mhervais, but this does not answer my question. The number of digits before, or after the comma is not the same as the number of significant digits.
For instance: I want a range of cells to contain 3 significant digits at maximum.

This would mean that:
0.0000432123 would be: 0.0000432
54321 would be: 543E2
3.12345 would be : 3.12





0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mhervaisCommented:
whatever you want to do (display it on excel, or get it from excel) there is no personalized display format to acheive what you want.

in this case, you will have to go thru a speciail routine running on one side (delphi), or on the other side (vba). As  Excel is being the server for OLE Automation, I recommend that this routine would be running on it.

regards Marc  
0
 
Phoenix_sCommented:
Leon321.

Use scientific notation format style

as below

Selection.NumberFormat = "0.00E+00"

that will give you the 3 digits of significance and the appropriate power after the E whether it's - or +


The puny little snippet above is from a VBA macro but should be simple enough to apply to a range via ole automation.

HTH  cheers!
0
 
Phoenix_sCommented:
oh yah... I do agree with mhervais as to the format you desire... you can't have your cake and eat it too unless you want to code a cell handling routine to format the cell based on it's contents, as you outlined to mhervais.
0
 
mhervaisCommented:
still listening
0
 
leon321Author Commented:
Dear Phoenix_s,

setting the numberformat this way, does not give the right results. For instance:
my values are:
12345,12345
1234
12,3456
1,6
0,06666666
0,06

One possible way to display these values with 3 significant digits would be:
123E2
123E1
12,3E0
1,6E0
666E-4
6E-2

When I set the your proposed number format, excel gives me:
001E+04 (1 significant digit, should have been 3)
1234E+00 (4 significant digit, should have been 3)
012E+00 (2 significant digit, should have been 3)
002E+00 (1 significant digit, should have been 2)
667E-04 (correct)
600E-04 (3 significant digit, should have been 1)

0
 
leon321Author Commented:
Phoenix_s,

Well, Though It's not exactly what I wanted, I'll give you the points for your help.

Your approach ALWAYS uses 3 significant digits. So 0.06 (which is 1 significant digit) becomes 6.00E-02 . Of course this is not the same, for instance when I measure a time period and tell you that it's 1 hour, I could have measured something between 0.30 and 1.29 hours. Compared to 60 minutes, which is between 59.30 and 60.29.

So 0,06 is not the same as 6.00E-2 (the nulls are not measured, but added by excel).

I guess it's just not possible with excel. Thanks for helping!

0
All Courses

From novice to tech pro — start learning today.