0

I have the following data:

Date    A   B   C
2012/07 7   6   0
2012/08 9   4   0
2012/09 9   3   0
2012/10 14  2   1
2012/11 9   16  0
2012/12 0   14  0
2013/01 7   9   1
2013/02 8   13  1
2013/03 16  62  16
2013/04 7   12  4
2013/05 10  11  1
2013/06 6   37  4

I want to make a line graph from these data, but I want it to show percentages of line total (A + B + C) instead of the absolute values. How can I do this directly, without resorting to intermediate cells where I'd insert formulas to calculate the percentages or adding a line total column?

So the end result should look like this:

enter image description here

But I don't want to have to "manually" create cells like these:

        A   B   C
2012/07 54% 46% 0%
2012/08 69% 31% 0%
2012/09 75% 25% 0%
2012/10 82% 12% 6%
2012/11 36% 64% 0%
2012/12 0%  100%0%
2013/01 41% 53% 6%
2013/02 36% 59% 5%
2013/03 17% 66% 17%
2013/04 30% 52% 17%
2013/05 45% 50% 5%
2013/06 13% 79% 9%
Waldir Leoncio
  • 10,853
  • 19
  • 77
  • 107

1 Answers1

2

Use Named Ranges.

First, define the name "Total" as =B2:B12+C2:C12+D2:D12

Then, define three names "PctA"=B2:B12/Total, PctB etc.

Then, define a name "Dates"=A2:A12

Insert a line chart and enter the 3 pct names as the data series. Put in the names as Sheet1!PctA, etc. - Excel won't accept the names without a sheet reference.

Do same for Dates as the horizonal category range.

chuff
  • 5,846
  • 1
  • 21
  • 26
  • These named range approaches are always very clever and all that. I would suggest however that you avoid them, unless you're using the named range to capture a resizable data range. The problem is that it's hard to debug them; if the data changes you may have to maintain them; and if someone else (or you in 6 months) wants to adjust them, it takes a long time to figure out what was done. Worksheet space is cheap and can be scrolled out of sight. – Jon Peltier Nov 07 '13 at 17:48