Gráficos cascada (Waterfall) con valores negativos

Hace poco más de cuatro años atrás publiqué este post sobre cómo crear gráficos de tipo "Waterfall" ("Cascada" o "Flying Bricks") con Excel.
Este tipo de gráficos permite ver el efecto acumulado de valores positivos y negativos en una secuencia. En el post mostramos este ejemplo, partiendo de los resultados anuales de una cadena de tiendas

mostramos este gráfico para mostrar el aporte de cada tiena al cambio en el resultado anual de la tienda

Este gráfico lo creamos usando el gráfico de columnas apiladas elaborando los datos con una tabla auxiliar (las fórmulas y técnicas pueden verse en la nota del enlace, también podrán descargar el archivo del ejemplo).

Esta técnica tiene un inconveniente, como me lo hace notar uno de mis lectores. Los valores de los cambios no cruzan el eje horizontal. Para demostrarlo, supongamos que nuestra cadena de tiendas ha tenido un año 2013 terrible

Aplicando la técnica que usamos hasta ahora obtendríamos este gráfico

La columna ("ladrillo") tendría que aparecer cruzando el eje horizontal, entrando en la zona de los números negativos:

Para solucionar este problema tendremos que cambiarlas fórmulas en la tabla auxiliar. Esta es la tabla del modelo sencillo

Esta es la nueva tabla auxiliar sobre la cual construimos el gráfico

A diferencia del modelo sencillo, calculamos dos columnas para los valores positivos (Positivo +; Positivo -) y dos columnas para los valores negativos (Negativo +; Negativo -). "Positivo +" y "Negativo +" para los valores que se mostrarán por encima del eje horizontal; "Positivo -" y "Negativo -" para los valores que aparecerán por debajo del eje de la X.

Las fórmulas de las columnas G y H son obvias.

La fórmula de la columna I (Acumulado) es:

=MAX(0,MIN(SUMA(G$3:G3),SUMA(G$3:G4)))+MIN(0,MAX(SUMA(G$3:G3),SUMA(G$3:G4)))

Esta fórmula crea la parte "invisible" de la columna.

La fórmula en la columna J (Positivo +): =MAX(0,MIN(SUMA(G$3:G4),G4))

La fórmula en la columna K (Positivo -): =-MAX(0,G4-J4) (prestar atención al "-")

La fórmula en la columna L (Negativo +): =MAX(0,M4-G4)

La fórmula en la columna M (Negativo -): =MIN(0,MAX(SUMA(G$3:G4),G4))

Para crear el gráfico seleccionamos primero el rango F2:F8 y manteniendo el botón Ctrl apretado seleccionamos el rango H2:N8. Seleccionamos el gráfico de columnas apiladas

El último paso es aplicar los formatos necesarios.

El archivo se puede descargar aquí.