Chart that would analyse the results of a survey

[renix]

Hi,

I would like to create a chart that would analyse the results of a survey in particular manner.
There were 6 questions with possible values 1-5. For each question, I want to show the range of answers (for example 2-4, then a diamond showing the average value).
I have reviewed what is on offer in excel and the stock chart looks like something I want but I can't get it to work?

Thanks

Renata

[oeldere]

First I used the macro in the file, to rearange your data.

2) I used Vlookup (and a table) to determine the range

3) I used pivot table to get the result.

I made 2 pivot table to give you an idea.

4) I made an graph of that data

See the attached file.

[renix]

Thanks oeldere, however is it possible that the 'y' axe has values 1-5 whilst the 'x' has the Q1-Q6?
I would like to show the results in a chart similar to the stock charts?

[oeldere]

Not sure, what you would like to see in the chart.

So I took a guess, Maybe like this?

[renix]

Similar to the one on sheet 3 - the line showing the range for the particular question (ie if the question answers were from 3-5, the line would show it and the diamond showing the average value). Can this be done?

[oeldere]

I think I can't help you enough with the chart part.

Maybe another forummember has an solution for you.

[Andy Pope]

You may need to do something with the zero data values if they are distorting the results because they represent DK/NA

[oeldere]

@Andy Pope

Thanks for stepping in, and give the asked anwer.

[renix]

Thanks guys!

Andy, that's perfect! :-)