# Column chart with multiple variables

1. ## 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?

2. ## 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

3. ## 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.

4. ## 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?

5. ## 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.

6. ## 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!

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

#### 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