Help with excel chart 3

[bluebird4027]

Hi - I'm trying to produce some sort of chart to show the following information and i'm just not getting anywhere!

I work in a special school and our students work towards Milestones. These are graded as 1a, 1b, 2a, 2b, 3a, 3b, 4, 5, 6, 7, 8. I need to show that 'bob' has reached level 7, 'sarah' has reached level 3a etc and I can't seem to get the info the right way round to get the chart to be correct.

(I know this is probably very easy but I'm completely blank!)
Thanks in advance
Sarah

 

[Welshbird]

Bluebird - what kind of graph are you trying to design here? Or is it just a case of getting that data to be 'visible' in some way?

Is this data over time or as a snapshot ie. do you want to track changes in the grade or just show where they are now?

I'm happy to try and help but I'm having difficulty really understanding what you're aiming for as an end result.

Ta.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bluebird4027]

Hi there - I thought I would end up with the data being shown in a normal Column chart - which excel will do as long as the milestones are just numbers. It doesn't seem to like it when they are 1a, 1b, 2a etc. It would be useful to be able to track the changes but for now I would be happy with a snapshot.
thanks for this

 

[SkipVought]

Hi,

You could make the levels
[tt]
1.0
1.5
2.0
2.5
...
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[bluebird4027]

Hi Skip - no as the Milestones are a bit like basic exams - so the levels are 1a, 1b, 2a, 2b, 3a, 3b, 4, 5, 6, 7 & 8.

 

[Welshbird]

I would be tempted to add another column with Skip's suggestion in, and then use the milestones as 'labels' with the 1.0, 1.5 as values for the graphs.

Then you get to be able to draw the chart, but the axis still shows the correct figures for you.

Does that help - or do you need more?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bluebird4027]

....but wouldn't the axis then show the 1.0. 1.5 ect which is not the correct figure? I don't understand why you just can't change them!!!!

Thanks for the help - I have every faith in you all!

 

[Welshbird]

You are right - this is more difficult than I thought!

I think my quick answer to this would be to use the suggestion of 1.0, 1.5 etc and provide a key on the side of the graph showing what each score means.

Excel obviously can't work out where 2b is meant to be on an axis as it simply isn't a number.

Do you need to show the name of each child on this?

If not, then the Milestones along the bottom and the count of children at each would show you something visual if that helps....

I'll keep thinking though in case I come up with a better suggestion.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bluebird4027]

Skip/Fee

thanks for all your help - if you have any more thoughts could you let me know please?
Cheers

 

[Welshbird]

Bluebird:

Dependant on your version of Excel this might be helpful.

http://office.microsoft.com/en-us/help/HA011109481033.aspx

It talks about creating histograms of studen results with non-numeric data.

It may not be what you need, but I'm afraid I'm short of time to look at the moment, so is probably worth a quick check.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bluebird4027]

Hi guys - think I've found a work round/cheat (at least I can produce a graph and hand it over!)

It isn't perfect and I haven't solved the problem by any stretch so if you could keep on thinking that would be great.

I can uncheck the the Y axis box so it doesnt show, move the graph over a bit and then insert a text box....like I said - it's a cheat and not the way it should be done but it'll work for today!

Thanks again

 

[bluebird4027]

Thanks for that - I don't think it's really what I'm after but I really appriciate your help

 

[Welshbird]

The Micro$oft site doesn't find anything when you search for 'non-numeric axis' or 'qualitative axis' which leads me to think that it isn't standard to do this in Excel.

There may be addins - xla's or VBA that you can use, but you're throwing me out of my depth for me to go there I'm afraid.

I'll keep pondering though. You just never know.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bluebird4027]

Your a star - thanks...I might try emailing micro$oft and see if they come up with anything

Cheers

 

[SkipVought]

When I think of milestones, a gantt chart comes to mind.

You can make a gantt chart using a stacked bar chart and a series of values values that correspond to the grade.

You data would look something like this...
[tt]
Name off1a 1a off1b 1b off2a 2a off2b 2b off3a 3a off3b 3b off4 4 off5 5 off6 6
Billy 10 0 10 0 10 0 10 0 10 5 10 0 10 0 10 0 10 0
Sally 10 0 10 5 10 0 10 0 10 0 10 0 10 0 10 0 10 0
[/tt]
Billy is in 3a, Sally in 1b

The offxx series have NO FILL/NO LINE
Each grade series has a separate fill color. It ONLY displays when there is a value greater than zero.


Skip,
[sub]
[red][/red]
[/sub]

 

[Welshbird]

Skip - That's an excellent solution! Hava star.

(Bluebird - I hope you don't have too much data to alter - presumablu you can vlookup or summat to speed it up. Obviously post back if you need help re-formatting)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bluebird4027]

thank you so much - the only thing is i just don't get it!!! i think i'm out of my depth.... why do you need to have the first 'off1a' column with 10 in it and then have the second 1a column? sorry for being thick and thanks for sticking with me....

 

[Welshbird]

I've tried it with the following data:
1a 1b 2a 2b 3a 3b 4
Sam 0 0 5 0 0 0
Billy 0 0 0 5 0 0
John 0 0 0 0 0 0 5

and got a graph - I chose a bar chart and the second one along.

This gave me the milestones up the side, and a bar in a different colour for each child. Maybe this helps?

(I think our problem here is that we are not sure exactly what you want to show in your chart, so its difficult to find the best solution. If you can give us some more input we may be able to find a better solution)


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bluebird4027]

if you were to do a very basic graph (below)with the numbers being the 'score' in the test, you would get a simple bar chart.

bob 1
sam 5
pete 2
jane 4
chris 6
sarah 2


I would like a simple bar chart but the 'scores' are from the range 1a, 1b, 2a, 2b, 3a, 3b, 4, 5, 6, 7, 8 and excel
doesn't recognise these 'score' as they are alphanumeric....

So the data would look like
bob 1a
sam 5
pete 2b
jane 4
chris 6
sarah 2b

 

[SkipVought]

"...why do you need to have the first 'off1a' column with 10 in it..."

Actually you don't need the FIRST off1a column, but to simulate a gantt chart milestone, that's a way to do it. Otherwise all the milestones stack up in the same position, rather than progressing to the right as milestones do in a gantt chart.

Skip,
[sub]
[red][/red]
[/sub]