Forum Discussion

Diego André Sáenz Peña's avatar
Diego André Sáenz Peña
Copper Contributor
Jun 12, 2018

Normalize two different scales on waterfall graph

Hi,

 

I need to have an equivalent unit for two different scales on a waterfall chart. I need to be able to visualize the magnitude and size of the problem in a comparable way.

 

See this example:

 

The green cells are percentages and the blue ones are decimals. As you can see for example on freshness (4.52%) in the waterfall chart it barely shows a bar (almost zero), however this 4.52 difference is a massive delta for that indicator (as well as water replenishment with a delta of 16%). Is there a way to compare percentages and integers/decimals so that I can see the improvement opportunities in each of the indicators?

 

So, the problem is that the graph compares to the other indicators the have non-percentage delta, since it's a scale that goes 200% by 200% (2 by 2).

 

Also I did a current state and end state, that is the sum of the actual indicators and the target indicators respectively. However I am summing different units, is there a way to standardize that?

 

FIle: http://www.mediafire.com/file/f33scpcyq9mab1n/TOMS.xlsx

 

Charts & Visualizing Data
tips and tricks

1 Reply

Sort By
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 12, 2018

    Hey Diego André Sáenz Peña-

     

    You can create a few Named Ranges and add in a worksheet event to help with the chart.  The short snippet of code will need to be pasted in the "Chart" worksheet module:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("rngWaterFallMin")) Is Nothing Then
        Me.ChartObjects("chtWaterFall").Chart.Axes(xlValue).MinimumScale = Target.Value
    End If
End Sub

    1. Create a named range cell to control the minimum axis (Note: this could be a formula....) 

    2. Name the range "rngWaterFallMin"

    3. Select the chart

    4. Name the chart "chtWaterFall"

    5. Now when you change the value of the "rngWaterFallMin" cell the value will automatically update the chart.  Changing it to make the bars appear larger or smaller based on your new specified minimum value.

     

    You can try this in the example file.  Since I cannot post an .xlsm file due to security risks you will have to add the code snippet to the attached file as specified in the instructions....

     

     

Resources