Need chart generating advice!

  • Thread starter Thread starter tdmatte
  • Start date Start date
T

tdmatte

Hi,
Let me thank you in advance for any assistance you may provide.

I am new to making charts and am not having much luck using the M
Excel help. I need to make a chart displaying the following data:
I have 5 products that I invited people to put in order of importanc
to them. I now want to create a few charts to display the information
I have the excel spread sheet set up where Column A is numbered 1 thr
5, across the top (Row 1) I have listed the names of all respondents.
Below each name I have listed the products in the order of thei
preference.
How can I now use this data? I need to know the rank (percentage) of
product #1, #2 and so on, and display it in a chart. Is this possible
If so, how
 
Shame you did not plan before entering data. It would have been better to
have names of responders on row 1 and in A2:A5 the names of the products.
Then under each name enter the persons ranking (1 thru 5) for each product).
I expect it would have been easier to enter the data this way from a
questionnaire and certainly easier to analyse.

But all is not lost. In row 1 ( in B2:F2) I have the names of 6 people (I
expect you have more but the method can readily be expanded). In A2:A6 I
have the numbers 1 thru 5. Under each name I have the products listed in
order of preference. SO this data looks like yours.

In A9:A13 I have the names of the 5 products (I happened to put them in
alphabetical order because I am a Type A person!) ; in B8:F8 I have the
names of the people (copied from row 1). In B9 (under the first person, to
the right of the first product) I have the formula
=MATCH($A9,B$2:B$6,0)
I have copied this across to F9 and then down to row 13. This gives me a
bunch of numbers (1 thru 5). So under the first person, next to the first
product I have a 2 because Person 1 ranked that product as his second
choice.

In B15 I have the text Average and in C15 the text Rank. In A16:A20 I have
the list of products again (copied from above) In B16 ( next to the first
product) I have =AVERAGE(B9:F9) but one could just as easily use =SUM(B9:F9)
and I have copied this down to row 9. In C9 I have the formula
=RANK(B16,$B$16:$B$20). Now I have the ranking of each product. These
numbers, of course, range from 1 to 5. I am not such what percentage means
to you in this task.

I have made a column chart. I selected A16:A20., held down the CTRL key and
selected the range. Next I clicked the Chart Wizard and chose Column chart,
first subtype.

Try this and come back with questions. Happy to get mail at my private
email.
best wishes
 
Back
Top