Problem A: I have data that could be in the 1000s but sometimes could be 1 or 2. Users do not want to see a division of 1.5 (or any part of a whole) since the data is tracking incidents and there are never “half incidents.” How do I force the values to integers without losing the divisors for the high numbers (i.e. when there is 1000 maximum, I want to still see 250, 500, 750 divisors)?
Solution: Set a manual calculated maximum
{ =IF(MAX(AE15:AE26)<100,100,MAX(AE15:AE26)) } and then set the division number to 1. With the division number to 1, it often also looks better to remove the grid lines (in case the data for the charts sometimes is much higher).
Problem B: I want to display a line chart on top of a stacked bar chart because there is no combination chart with this option. My data for the stacked bar chart, however, is sometimes over 100 and sometimes under 100 and the extra space shifts the chart so that the two do not align. How can I make the layered charts always align?
(Layered line chart does not line up with stacked bar chart when maximum is less than 100)
(Layered line chart lines up with stacked bar chart when the maximum is over 100)
Note: If the charts are less than a magnitude different, the following method is a not-so-elegant-but-perhaps-acceptable workaround. In the example above, I have emphasized the issue of alignment by making the different much greater in which case a more-involved display control of multiple charts may be the only acceptable option.
Solution: Set a manual calculated maximum within an if() statement so that the maximum is always 100 or greater even if the stacked bar chart total is less than 100. (See above note for exception)
This “fix” may not be acceptable for the right visual consumption. What may need to be done when the data is more than a magnitude of 10 different is to work with display properties for two charts with different widths based upon the maximum chart height formula instead.
No comments:
Post a Comment