+ Reply to Thread
Results 1 to 6 of 6

Column chart with multiple variables

  1. #1
    Registered User
    Join Date
    10-31-2016
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Column chart with multiple variables

    Hello everybody,

    I'm trying to make a chart from a list of airplanes. Every year I need to have the top 3 airplanes, who transports the most amount of people.
    I have a data set, with raw numbers. But I can't figure out how I will be able to select the top 3 every year.
    I Have a rough example attached, but as you can see, the columns aren't attached to each other.

    Can anybody help me out?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Column chart with multiple variables

    Hello welcome to the forum.

    Take a look at the attached example. The chart is fed from some dummy data on the Chart Data tab to return only those values that meet the drop down criteria; top 1, top 2 etc. The formulas are in B3:Q10 on the Chart Data tab.

    Is this what you're looking to do?

    DBY
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Column chart with multiple variables

    It's not clear to me exactly what you want. It would probably be helpful to provide some hand-worked examples to illustrate what you are trying to do here.

    Are you familiar with the LARGE() function? https://support.office.com/en-us/art...b-01672ec00a64 My first thought is to put something like =LARGE(B$2:B$9,$AA15) (where AA15, AA16, and AA17 contain 1,2,3) into B15. Then copy down and across, note how I used a mix of relative and absolute references to make it easy to copy the formula. This will yield a matrix that consists of the largest three values for each year. Then you can chart those values on a column chart.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    10-31-2016
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Re: Column chart with multiple variables

    DBY, thats what I meant. But know I want to make a column chart. But only with the top 3 by year. So not like in my chart with the spaces between the bars. Do you know how i can change that?

    Thanks in advanced!

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Column chart with multiple variables

    You will need to reconfigure your data and use VBA to format the points of each series to match specific airline colours.

    I've used a formula to build a table of rank positions by year. This then allows for the population of 2 more tables.
    The first contains the data values for each o the top 3 airlines by year. The second contains the airline name.

    Create a column chart based on the data values.

    In order to get a series for each airline you need to add dummy series for each airline and move it to the secondary axis.
    You can format these airlines series as required.

    You will then need to run code to determine which airline is 1st, 2nd or 3rd for each year and then apply correct colour.

    I have left the original rank series in the legend so you can see how the chart is constructed but there is no reason why you can not delete legend entries.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    10-31-2016
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Re: Column chart with multiple variables

    Andy, this is exactly what I need. Thank you very much. Now I will try to understand every step you took.

    Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 07-12-2016, 12:30 AM
  2. Chart with multiple variables
    By sarinky in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-07-2014, 04:48 AM
  3. Help - Chart Multiple Variables
    By nangys in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-05-2014, 05:26 PM
  4. Create Bubble Chart with multiple variables
    By MiguelFernandes in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-16-2012, 09:48 AM
  5. Need help on Line Chart with Multiple Variables
    By aliwaii in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-30-2011, 04:34 PM
  6. Excel 2007 : Need to chart multiple variables
    By jscardi in forum Excel General
    Replies: 1
    Last Post: 03-11-2011, 03:09 PM
  7. Chart multiple variables
    By jscardi in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-11-2011, 11:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1