Charting Descending Values with Bars Coloured by Type

PJVV77

New Member
Joined
Aug 2, 2019
Messages
5
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Aug 2, 2019
Messages
5
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!
 

Forum statistics

Threads
1,081,984
Messages
5,362,554
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top