Waterfall chart with annual values at each end and variances as waterfall steps
I'm trying to create a waterfall chart, where the first bar in the waterfall is the absolute value for 2017, and the steps in the waterfall are the variances in each category. This should result in the summary bar being the absolute value for 2018.
My best solution so far is to create a new category for values in 2017, however, this means that the values in 2017 are unavailable for the variance calculation for each category.
Has anyone done this previously, or is there another way to create the 2017 bar at the start of the waterfall?
I've illustrated this problem in the attached excel file. Columns B, C and D are my dataset, and E to J are my beast mode columns/calculations. I have 6 categories A to F, and I create a new category G for 2017 values, so that G is the first bar in my waterfall, with an absolute value for 2017. However, to calculate the variance of categories A to F, for the steps in the waterfall, the rows in 2017 are now in the G category, and unavailable to categories A to F.