[SOLVED: workaround found, see bottom]
(Before you reply, I do understand that most decimal numbers can not be expressed exactly in binary format, even simple numbers such as .3. I'm trying to limit the number of digits after the decimal that are shown.)
I'm trying to cleanup the data table in a chart. The data has only 1 or 2 digits after the decimal point, is positive, and less than 100. Typical values are 10.7, 40.1, etc. I've added four 'threshold' horizontal line series to the chart. These are computed double precision values, rounded to 2 decimal digits.
Exhibit A, the Data Table
My problem is that the data values all fit nicely in the data table, but for the computed values, 3 of the 4 display more than the expected 2 digits. Here is what a column from the table looks like:
When actually printed on the Chart Sheet, three of the values are so squeezed as to be almost unreadable.
Since I had rounded the computed values to 2 digits, I had hoped that they would appear that way in the data table.
Exhibit B, the data series values:
Next, I examined the data series from the Chart Sheet. Here are the arrays for the 4 computed values (values were previously Round()-ed to 2 decimal digits)
(spaces added every 3 digits for readability)
={10.840 000 152 587 8,10.8400001525878,... 10.840 000 152 587 8} appears as 10.840 000 2 in the data table
={ 9.640 000 343 322 75,9.64000034332275,... 9.640 000 343 322 75} appears as 9.640 000 34
={12.710 000 038 146 9,12.7100000381469,... 12.710 000 038 146 9} appears as 12.71
={12.170 000 076 293 9,12.1700000762939,... 12.170 000 076 293 9} appears as 12.1700001
My observation: Looks like Excel is rounding the numbers to 7 digits (even though I previously Rounded to 2!), and then not printing trailing zeros.
Exhibit C, Round() and debug.print
To experiment with interaction between rounding and printing (at least, debug.print), I ran the following in vba editor, immediate window (my input is denoted by >>)
>> a1=10.8400001525878
>> debug.Print a1,round(a1,2)
10.840 000 152 587 8 10.84
>> a1=12.710 000 038 146
>> debug.print a1,round(a1,2)
12.710000038146 12.71
My observation: first value (10.84...) is printed as expected when rounded to 2 digits. Why doesn't the data table appear the same way?
Question: Is this just a quirk of data tables, or is there a way to fix this.
[SOLVED]
(Taking the time to post here always makes me think better!)
I found a solution. I had declared the computed values as Single; I've switched that to Double and now the rounding and printing in the data table works as expected.
(Before you reply, I do understand that most decimal numbers can not be expressed exactly in binary format, even simple numbers such as .3. I'm trying to limit the number of digits after the decimal that are shown.)
I'm trying to cleanup the data table in a chart. The data has only 1 or 2 digits after the decimal point, is positive, and less than 100. Typical values are 10.7, 40.1, etc. I've added four 'threshold' horizontal line series to the chart. These are computed double precision values, rounded to 2 decimal digits.
Exhibit A, the Data Table
My problem is that the data values all fit nicely in the data table, but for the computed values, 3 of the 4 display more than the expected 2 digits. Here is what a column from the table looks like:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Data1[/TD]
[TD]10.6[/TD]
[/TR]
[TR]
[TD]Data2[/TD]
[TD]12.4[/TD]
[/TR]
[TR]
[TD]Computed1[/TD]
[TD]10.8400002[/TD]
[/TR]
[TR]
[TD]Computed2[/TD]
[TD]9.64000003[/TD]
[/TR]
[TR]
[TD]Computed3[/TD]
[TD]12.71[/TD]
[/TR]
[TR]
[TD]Computed4[/TD]
[TD]12.1700001[/TD]
[/TR]
</tbody>[/TABLE]
<tbody>[TR]
[TD]Data1[/TD]
[TD]10.6[/TD]
[/TR]
[TR]
[TD]Data2[/TD]
[TD]12.4[/TD]
[/TR]
[TR]
[TD]Computed1[/TD]
[TD]10.8400002[/TD]
[/TR]
[TR]
[TD]Computed2[/TD]
[TD]9.64000003[/TD]
[/TR]
[TR]
[TD]Computed3[/TD]
[TD]12.71[/TD]
[/TR]
[TR]
[TD]Computed4[/TD]
[TD]12.1700001[/TD]
[/TR]
</tbody>[/TABLE]
When actually printed on the Chart Sheet, three of the values are so squeezed as to be almost unreadable.
Since I had rounded the computed values to 2 digits, I had hoped that they would appear that way in the data table.
Exhibit B, the data series values:
Next, I examined the data series from the Chart Sheet. Here are the arrays for the 4 computed values (values were previously Round()-ed to 2 decimal digits)
(spaces added every 3 digits for readability)
={10.840 000 152 587 8,10.8400001525878,... 10.840 000 152 587 8} appears as 10.840 000 2 in the data table
={ 9.640 000 343 322 75,9.64000034332275,... 9.640 000 343 322 75} appears as 9.640 000 34
={12.710 000 038 146 9,12.7100000381469,... 12.710 000 038 146 9} appears as 12.71
={12.170 000 076 293 9,12.1700000762939,... 12.170 000 076 293 9} appears as 12.1700001
My observation: Looks like Excel is rounding the numbers to 7 digits (even though I previously Rounded to 2!), and then not printing trailing zeros.
Exhibit C, Round() and debug.print
To experiment with interaction between rounding and printing (at least, debug.print), I ran the following in vba editor, immediate window (my input is denoted by >>)
>> a1=10.8400001525878
>> debug.Print a1,round(a1,2)
10.840 000 152 587 8 10.84
>> a1=12.710 000 038 146
>> debug.print a1,round(a1,2)
12.710000038146 12.71
My observation: first value (10.84...) is printed as expected when rounded to 2 digits. Why doesn't the data table appear the same way?
Question: Is this just a quirk of data tables, or is there a way to fix this.
[SOLVED]
(Taking the time to post here always makes me think better!)
I found a solution. I had declared the computed values as Single; I've switched that to Double and now the rounding and printing in the data table works as expected.
Last edited:
