CODEWITHSUNDEEP
excelgraphpercentagelinegraph
Waldir Leoncio
Waldir Leoncio

Reputation: 11341

Percentage graph from absolute values

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%

Upvotes: 0

Views: 1779

Answers (1)

chuff
chuff

Reputation: 5866

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.

Upvotes: 2

Related Questions