Charting Descending Values with Bars Coloured by Type

[PJVV77]

Hi, I have a set of data that has three colums; a centre identifier, the type of asset it is (each centre will be classified as an asset type and I have 5 different asset types) and the profit it makes.

I have seen a chart before where it shows each centre's profit as a bar (so you get a visual view of the profit distribution), but the bars could be in one of 5 colours (the various asset types). There were no gaps between the bars, so it was spaced equally and the legend showed the colours of the asset types.

Looking for help as to how to structure my data to get this done easily in Excel.

Thanks!
Pieter

 

[Gerald Higgins]

Hi, welcome to the board.

I think I might know what you mean, but I'm not completely sure.
If it's what I think, then you can probably do this by setting up your data in a certain way, and using a stacked column chart.

Let's consider a simple example of what I think your data might look like . . .

Centre ID.......Asset Type.......Profit
1..................A....................10
2..................B....................20

You need to set up some columns to drive your chart data, that will bring in the profit value only if it matches the asset type, like this...

Centre ID.......Asset Type.......Profit.........Profit Asset Type A........Profit Asset Type B
1..................A....................10.............10...............................0
2..................B....................20..............0................................20

You can use a simple IF statement to generate these values, for example
=if(B2="A",C2,0)
and
=if(B2="B",C2,0)
would be the formulas for the A type and the B type, making simple assumptions about the cell references.

Repeat for as many asset types as you have.

Then make your chart use these new columns as the source data for your stacked column chart.

 

[PJVV77]

Hi, welcome to the board.

I think I might know what you mean, but I'm not completely sure.
If it's what I think, then you can probably do this by setting up your data in a certain way, and using a stacked column chart.

Let's consider a simple example of what I think your data might look like . . .

Centre ID.......Asset Type.......Profit
1..................A....................10
2..................B....................20

You need to set up some columns to drive your chart data, that will bring in the profit value only if it matches the asset type, like this...

Centre ID.......Asset Type.......Profit.........Profit Asset Type A........Profit Asset Type B
1..................A....................10.............10...............................0
2..................B....................20..............0................................20

You can use a simple IF statement to generate these values, for example
=if(B2="A",C2,0)
and
=if(B2="B",C2,0)
would be the formulas for the A type and the B type, making simple assumptions about the cell references.

Repeat for as many asset types as you have.

Then make your chart use these new columns as the source data for your stacked column chart.

Perfect! Thought I was over-complicating it.... Thank you!