Solution to Charting Problem

[cosmica67]

Hi, All.

I am trying to come up with a solution to a problem I have with some data I need to chart.

I have attached the test data.

What I need to do is produce a line graph that uses and compares the data over a 6 year period for each subject, ideally one which I can somehow use a searching facility or lookup table if possible.

Would there be an easier way that just searching manually for the data, especially since there is a lot more data than is shown in the test data file?


The example data below shows what two data samples would look like for subject 1:


2011-2012 1 0.96
2012-2013 2 1.58
2013-2014 3 1.96
2014-2015 4 2.04
2015-2016 5 1.92
2016-2017 6 1.61


2012-2013 1 1.09
2013-2014 2 1.90
2014-2015 3 2.41
2015-2016 4 2.39
2016-2017 5 2.11
2017-2018 6 1.80


Thank you for looking,

Regards,

[AliGW]

Are you still using Excel 2016, or have you upgraded?

Can I just clarify? The two tables you show would in fact be just one, and you want this one summary table to change based on criteria you stipulate - is this right?

If so, it's quite easy to achieve with a drop-down selector box and formulae to populate the table. It will be even easier if you happen to have MS365!!!

[cosmica67]

Hi,

I am still using Excel 2016.

Yes, the two tables showing would be one summary table which I can change based on criteria.

Thanks,

[AliGW]

OK - thanks for clarifying.

[davsth]

It would be interesting what you want as a final products it is not so clear are you plotting years as a year or as a number

A lookup function gets the data point you are interested in
It changes depending which test you are interest in by using the match function to move to the column you are interested in

However i am not sure this is even what you want!

[cosmica67]

Hi,

What I need to try and create as a final chart is a line graph that tracks the data over a six year period.

The data in the sheet is data from different classes. So in 2011-2012 there was only Class 1 that had data, in 2012-2013, this same class now becomes class 2 as the class has moved up a year and so on.

So for example, if I look at the data in the sheet, I can manually select the data as for the 2 examples below:

2011-2012 1 0.96 (relates to class 1)
2012-2013 2 1.58 (class 2 has moved up from class 1 the previous year)
2013-2014 3 1.96 (class 3 has moved up from class 2 the previous year)
2014-2015 4 2.04 (class 4 has moved up from class 3 the previous year)
2015-2016 5 1.92 (class 5 has moved up from class 4 the previous year)
2016-2017 6 1.61 (class 6 has moved up from class 5 the previous year)


2012-2013 1 1.09
2013-2014 2 1.90
2014-2015 3 2.41
2015-2016 4 2.39
2016-2017 5 2.11
2017-2018 6 1.80


I'm not sure if I've explained it better or muddied the question!

Thanks for looking.

[MrShorty]

Assuming I understand what you are trying to do, here's what I did:

1) It looks like you want a way to "filter" the data by "cohort", but your database does not include an easily identifiable "cohort" column. Currently, each cohort is identified by year+class, but that changes each year (2011 class 1 become 2012 class 2 become 2013 class 3 and so on). I'm thinking that I would want a "cohort" column with a unique identifier for each cohort in the database. I enter "cohort" into G1. In the rest of column G, I enter a unique identifier for each cohort (for example, I enter "a" for 2011 class 1, 2012 class 2, 2013 class 3, etc. "b" for 2012 class 1, 2013 class 2, etc.). I did it manually because this is a small database. The biggest challenge for a large, existing database might be identifying any patterns and come up with formula/program that can assign unique cohort identifiers to each cohort. I have not explored this programming challenge (the question is focused on charting, so I'm hoping you either already have unique cohort identifiers or know how best to come up with those identifiers).
2) With a column of unique cohort identifiers, now I can create a chart and use filters to view the subset(s) of the data I wish to see.
2a) Select the table from row 2 down -> Insert line chart. If the Chart defaults to "Move and size with cells", format the chart so that it does not move or size with cells.
2b) Select the enter table (row 1 down) -> Data -> Filter
3) Select the dropdown next to "cohort" and choose the cohort whose data I wish to see in the chart. As long as the "hidden and empty cells" setting for the chart is set to "don't show", then I will be left with a chart showing only the data for the chosen cohort.

Will something like that work? Do you already have cohort identifiers, or do you need help coming up with them?

[cosmica67]

Hi,

Thanks for the reply.

I managed to do something as you suggested, using a helper column (giving me a unique identifier for each class for each academic year). This worked using the vlookup function to search for classes within the unique identifier column.