Sparklines for Excel Chart Module:
Variance Chart
Environment:
Windows XP – Excel 2003 SP 3
Problem Description:
Applies to Legend Usage for Variance Chart
When passing values to the varichart function, if the length of the underlying value calculated is greater in length then the size of the bar of variance, the value will be truncated or will just not appear.
In addition, if the calculation of the variance has a repeating decimal it will repeat the digit to the end of the variance bar never displaying the decimal point.
Reproduction Steps
Set the column width to 25 and put the following formulas into the column.
Repeating Digit Issue for Positive Variance:
=varichart(280/150-1,,,,,,TRUE)
Repeating Digit Issue for Negative Variance:
=varichart(35/150-1,,,,,,TRUE)
Truncation of Positive Values
=varichart(171/150-1,,,,,,TRUE)
Not Showing Any Legend Value
=varichart(155/150-1,,,,,,TRUE)
Expected Behavior
When invoking the Legend, some value should always be printed in the variance chart. Today this does not seem to happen do to two conditions:
1. The function accepting the value for the legend does not limit the number of significant digits thus the entire chart is full with repeating values when a format like 1/ 3 is used.
2. The function is trying to print the value inside of the bar chart thus if the value is too small to be represented on the bar chart it will not actually show.
Possible Fixes
Alternative 1:
Limit the number of significant digits that can be passed to the variance function by recasting the value and instead of printing the legend inside the bar, print the legend at the max or min point thus a value will always print.
Alternative 2:
Limit the number of significant digits that can be passed to the variance function by recasting the value and instead of printing the legend inside the bar, reserve space on the left hand side of the chart to print the legend. (Note: this alternative would then need a corresponding change in the function InvScaleline and Scaleline in order to accommodate a legend to the left hand side of a variance chart. Alternative 1 would not need this change.)
Grant
2008-10-30
Screen Print Showing Issues
Grant
2008-10-31
Fabrice Rimlinger
2008-10-31
Fabrice Rimlinger
2008-10-31
Solved by adding a transparent text layer.
If variance<0, the text is displayed on the right
If variance>0, the text is displayed on the left
user can choose to display legend or not (for small values) using a simple if formula for the "legend" parameter : if (abs(variance)<0.3;"false";"true")
Fabrice Rimlinger
2008-10-31
Grant
2008-11-04
Grant
2008-11-04
Corrected.
Fabrice Rimlinger
2008-11-07