Charting a competative analysis survey

**tekman** · 06-28-2008, 11:18 PM

I have been tasked with compiling and charting the results of an internal competative analysis survey. I fairly experienced with excel and automating it with the exception of charting....

Here is the basic setup (see attached for sample data)

For each of 3 categories, we are asked to "rank" competing products, with the option to have "write in" ballots. There will be up to 200 surveys to collect and organize.

Where:
1 = Best (our biggest competitor)
5 = Worst (A non player)

Although I can get the data OK, I am having a problem creating a decent pivot table / chart to reflect this data due to the possibility of a variable field (the write in ballot). Theoretically I guess there are an unlimited number of possibilities for the write in, but realistically there will be no more than 3 or 4 (I made room for 10 in the macro that consolidates the data)

My attempts at charting this data are not even worth posting here as an example... I am looking for any suggestions for both the type of graph, and how to organize the data within the graph - possibly with all 3 graphs on on a single tab (Although that is not 100% neccessary)

I appreciate any help or suggestions....

EDIT: Using Excel 2003
EDIT_2: Both Tabs show the same data is differnet formats, which ever is easier to work with is fine

Lee

**Andy Pope** · 06-29-2008, 05:21 AM

In terms of charting the best way would be a simple bar chart that itself is ranked.

How are you going to determine the resulting rank of the 200 respondents?

**tekman** · 06-29-2008, 10:41 AM

That was one of the challenges I have.

I was debating the best way to detemine ranking.... I was thinking one of a few ways...

Average the ranking for each brand and then displaying the results

or

Give "points" for each attained rank (1 = 5 points, 2= 4 points, etc) and then chart the total number of points each brand has. This would definatly be easier if I "inverted" the results before putting them on the datasheet, then a sum of the points would be staight forward.

or

One of the side by side bar charts that will show all data with "groups" of data, one major column for each ranking with a "sub" column for each brand and the count of number of votes for rank.

I hope this make sense :-)

I just could not think of the most effective method to display the data, (Which I know can be subjective) If I had a specific goal in mind I would at least have something to shoot for and experiment with, but with my limited charting experience it was/is hard to visulaize the results I am looking for.

Thanks for your time

Lee