value precision 2

[claudehenri]

hi

I'm writing variables (defined as single) to excel spread sheets but for some reason the the actual value in the cell is a double. It doesn't seem to make much sense to me. I have been able to prevent this before but it was pretty cumbersome (i've forgotten exactly how at the moment), it also seems unneccessary. how do i stop this happening.

Claude-Henri

 

[xlbo]

Why not just format the cell to the precision you want instead ??

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi claudehenri,

This is probably a silly question, but how do you know it's a double in the cell? And why does it matter?



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[claudehenri]

I know its a double because the precision goes to 16 or whatever decimal places doubles go to, instead of 6 or 7 for a single.

These extra decimals aren't really a worry except that i'm have the possibility of reading these back into my program at a later on. when you show it in the text boxes its pretty messy, and i don't want to check/convert all 150 or so variables i have.

There also a problem when i'm using MAX in the spread sheet with calculated values. They are suppose to be limited to a certain value, but occassionally its value is "value" +0.00000000000001 which sends the values i'm trying to collect all haywire.

So thats my real problem

Claude-Henri

 

[TonyJollans]

Hi Claude-Henri,

I'm afraid floating point arithmetic is inexact; there is nothing you can do about it. Scaled integers are the only way to get absolute precision but you have to do more work to use them.

You may find it helps to do as Geoff says, and format the cells with the number of decimal places you want. And then to select "Precision as Displayed" under Tools > Options > Calculation.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[claudehenri]

I have no real problem with setting the format (I've actually aready done the that before). The "precision as displayed should do my max() problem though.

The only thing is that its pretty stupid that a variable which = 1.000002 should print to the cell as 1.0000019999999932578 or 1.000002000001354994 for instance. where does all the gobbledy gook gome from?

C-H.

 

[byundt]

You work in base 10, so a value of 1.000002 makes perfect sense. In base 2, which the computer uses, you can only approximate 1.000002 as

=1+1/2^19+1/2^24+1/2^25+1/2^29+1/2^30+1/2^32+1/2^33+1/2^34+1/2^35+1/2^37+1/2^38+1/2^39+1/2^40+1/2^42

which equals 1.00000999999999

Integers may be stored exactly, but floating point numbers are 15 digit approximations (in base 10) with and exponent between +308, -307. 1*10^308 is an awfully big number, and 1*10-307 is exceedingly small.

Excel defaults to Long integers and Double Precision floating points. If you declare a value as Integer or Single, Excel still allocates enough space for a Long or Double.

 

[claudehenri]

thanks byundt

that makes a bit more sense

Claude-Henri

 

[byundt]

If you want to expand the representation of a number as powers of 2, try putting the numbers 1 through 42 in column A, the value sought in cell D1 and either a 1 or 0 in cell B1.

In cell D2, you will have a normalized floating point number, which has been multiplied or divided by a power of 2 to get it in the range between 1 and 2. The normalized number equals:
=D1/(2^INT(LN(D1)/LN(2)))

The balance of column B will contain a formula for the presence or absence of a contribution from that power of 2
=IF(SUM(B$1:B1)+2^-A2<=D$2,2^-A2,0) formula in B2, copy down

Column C will contain the running total
=SUM(B$1:B1)*(2^INT(LN(D$1)/LN(2))) formula in C1, copy down

Once you format column C to show at least 15 digits, you can see how each incremental term contributes to the total.

Of course, the formula loses validity beyond the floating point precision built into Excel (and Windows PC).