Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to show Levels of data on 1 Graph? (XP)

    Hi,

    The attached Microsoft Excel file has 3 tables

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to show Levels of data on 1 Graph? (XP)

    I would think this would get very "busy" with so much info on 1 chart:
    Your example is not complete but If I assume the "example" typical:
    1) Countries = 4
    2) Region / countries = 8 (= 4*8 = 32 total)
    3) Counties / region = 6 (4*8*6 = 192 counties)

    I think you will be hard pressed to find 192 different "styles/colors" to differentiate them

    One way is to use a stacked column with 3 categories:
    Country, region, county
    The country stack would have 4 stacked columns
    The region would have the approx 32 regions stacked, thus the regions for each country would be next to the country and the same total size
    The Counties are would have the 192 stacked columns and they would line up with the region

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to show Levels of data on 1 Graph? (XP)

    Here are 2 examples (I made up some data since your set wasn't complete). it is adaptable to other datasets
    The first sheet is the raw data I made up
    Then a chart of the stacked col (like in mention in <post#=341593>post 341593</post#>). This might need to be formatted differently as desired, I did not play with the colors. The data "setup" is in a separate sheet, though it uses the raw data and sumifs to get the sum for the region and country (you only need the value for the county and some indication ow the region and country.

    I also made a "hierarchy chart". This is a simple chart, but more complicated to setup. You can change the "spacing" between counties of the same region, of different regions and different countries (I used 2 within region, 3 within country, and 5 in different countries)

    It has 4 ranges: countries, regions, counties, all just different points and then one (not shown in legend) the lines connecting the points.

    The data for the Y-values of the points is extracted from raw using sumif, The "X" for the counties is calc'd using the criteria above (2,3,5) though this can be changed easily. The X-values for the country and region is calc from the average of X-values for the counties in the country or region.

    The lines use an INDEX to grab the info from the X,Y pairs to make one large XY region. I used 1 Inded for the entire thing and copied it down. I allowed my desired "blanks" to be errors and then selected the errors (goto special - formula errors) and deleted them. I needed blanks to not have a line from 1 county back up to the next country.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to show Levels of data on 1 Graph? (XP)

    Wow, that "hierarchy chart" is amazing!

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to show Levels of data on 1 Graph? (XP)

    Thank you.

    It really isn't that difficult to setup. There is a lot of "duplicate" chart data since each country and region has an identical point for every county in it, but it made the setup more general and it doesn't matter to the chart. Even the lines have a lot of duplicates. It was easier to use "brute force" and repeat everything than to work up code to only plot the "bare minimum requirements"

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    London, Middlesex, England
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to show Levels of data on 1 Graph? (XP)

    Thanks very much Steve. I'll try to go through that & grab a good understanding of what you did.

    Regards,

    Bob.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to show Levels of data on 1 Graph? (XP)

    Steve,

    I agree with Hans' sentiments. That chart style is very nifty. To me, it's like a data structure tree, but with actual/ useful values shown graphically. Is this a standard graphical form, or your own invention? You mention lots of duplicate data. Is this analagous to using a data set in the form of a "flat file"? That's how I interpret your reference.

    nice!

    Alan

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to show Levels of data on 1 Graph? (XP)

    I thought of it as a structure tree. I've seen this chart before, though never in excel (that I recall). In some sense I "made it up" to answer the question posed, though (I don't think) there is anything "overly original" in the technique: It is just using XY coords to "Draw lines" in an XY scatter. I have been doing this technique even when I was using Lotus123 over a decade ago.

    The "duplicate data" I refer to is just that: If you look at the data there are 36 rows in the data and 36 county points plotted. It "appears" there are 12 regions plotted and 3 countries plotted. That is incorrect, there are 36 regions plotted and 36 countries plotted. They just "overlap" one another. Each of the 36 data points give 36 "unique" county points, but only 12 "unique" regions (1 is the overlap of 4 points, 1 3 pts and 1 5 points). There are also 36 points plotted for the countries (only 3 unique ones). As I said I was too lazy to code to get the unique values, it works just as easy to do it this way.

    It was (to me) an easy way to setup and plot without having to manipulate the data. The raw data setup also allows pivot tables. Ideally if you wanted to get reid of the "flat aspect" from the data, I would create a region table and a country table and in the raw data instead of duplicating the names in the table, I would use a lookup to the other tables, this makes changing the names easier.

    Steve

Posting Permissions

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