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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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
Phoenix_sCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.