|
|
Peltier Tech
Books at Amazon.com
Buy me a coffee If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee. |
Simple Excel Conditional Chart Example 2.
In my Simple Conditional Chart Example, I show how to make a conditional column chart in which the height of the entire column is based on the value represented by that column. This example shows how to change the color of different levels of the column. The section of each bar between 0 and 2 is blue, between 2 and 4 is green, and on through yellow, orange, and red. The trick is to use worksheet formulas to split the values into increments of 2, and to plot these increments as stacked columns.
The example uses the following data in A1:G9. | A B C D E F G --+----------------------------------------------------------- 1 | 0 2 4 6 8 2 | 0 to 2 2 to 4 4 to 6 6 to 8 8 to 10 3 | 6.85 A 2 2 2 0.85 0 4 | 8.58 B 2 2 2 2 0.58 5 | 7.53 C 2 2 2 1.53 0 6 | 4.53 D 2 2 0.53 0 0 7 | 2.27 E 2 0.27 0 0 0 8 | 1.14 F 1.14 0 0 0 0 9 | 0.96 G 0.96 0 0 0 0 Column A has the actual values, while column B has the category (X axis) labels. Row 1 has my cutoff levels for the formulas, starting at zero, and incrementing by twos. Row 2 has the series names. A1:B2 are blank. The hard work is being done by the formulas in C3:G9. Cell C3 contains this formula: =IF($A3<=C$1,0,MIN(2,$A3-C$1)) If the value in Column A is less than the cutoff, the cell contains zero (the colored column will not show in the chart); if the value is greater than the next higher cutoff, the cell contains 2, so the entire segment of the colored column is visible; and if the value is in between, the cell will contain the amount over the cutoff, and part of the colored column will appear, up to the total value in column A. The placement of the dollar signs assures the correct behavior when the formula is applied to the entire range. The formula is applied to the entire range in one of two ways:
Select the range B2:G9, and Start the Chart Wizard. In step 1 choose the stacked column chart type, and in step 2 make sure the Series in Rows option is selected. Format the colors the way you want, and you're done. |
Peltier Technical Services, Inc.Custom Solutions | Commercial Utilities | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2009. All rights reserved. |
