( DataZ × scalingFactor ) ÷ ( | DataZmax - DataZmin | ) - DataZ
You may sometimes require bubbles in a bubble chart to have smaller or larger radii than the chart renders them with originally. A common solution to this problem is to multiply (or divide), by a constant factor, the data values in the data column bound to a bubble chart’s ColumnZ , which are the data values used to determine the radius. However, if you attempt to use this approach, you may obtain results that are not satisfactory. This is due to a feature of our bubble charts, designed to prevent "overly large" or "microscopically small" bubbles.
A bubble chart includes proportionality logic that keeps bubbles proportional in size to each other, reflective of the data values in a bubble chart’s Column Z. Applying a constant factor, or divisor, across all data values in a column does not alter the values' proportional relationship to each other.
The following table shows an example of the situation:
You will see that in Table 1, the ratio of each bubble to the others remains the same as it was before the data values in Column Z were halved. Consequently, the ratio of the bubbles' radii to each other remains unchanged.
Working around this functionality requires that the data used in Column Z be adjusted by a non-constant factor, as shown in Table 2. Developers applying this approach are cautioned that while controlling bubble size conveys greater visual control over a bubble chart’s presentation, it also distorts the informational content inherent in the bubbles' radii. You may find sacrificing accuracy for aesthetic considerations an acceptable compromise in the context of your application, but you should be aware that you are reducing the accuracy of the chart.
This second transformation alters the ratio between bubbles and therefore exhibits an effect on the radii of bubbles rendered by a bubble chart.
A general-purpose formula used for making these smaller bubbles would be:
( DataZ × scalingFactor ) ÷ ( | DataZmax - DataZmin | ) - DataZThe variables in this formula are as follows:
DataZ - Current data value in the data column identified by the ColumnZ property of BubbleChartAppearance object for the current bubble.
DataZmax - Maximum data value in the data column identified by the ColumnZ property of the BubbleChartAppearance object.
DataZmin - Minimum data value in the data column identified by the ColumnZ property of the BubbleChartAppearance object.
scalingFactor - A factor the developer multiplies against all data values in the data column identified by ColumnZ before binding the chart data to Chart.
In code, you would implement this formula as:
Data_Znew = (Data_Z * scalingFactor) / (Abs(Data_Zmax - Data_Zmin) - Data_Z)
As illustrated here, scalingFactor would be 1.0, but it can be increased to moderate the intensity of its effect and meet the visual needs of the presentation.