Results 1 to 3 of 3

Thread: Charting Descending Values with Bars Coloured by Type
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Charting Descending Values with Bars Coloured by Type

    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

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Charting Descending Values with Bars Coloured by Type

    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.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Charting Descending Values with Bars Coloured by Type

    Quote Originally Posted by Gerald Higgins View Post
    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •