Plotting points on lines

[anke12]

Hello,

I'm struggling with the following task in Excel. I'm using Excel 2007.

I have several scales from a questionnaire that I send to groups of 10 people.
Each scale has a range on which people can score, e.g. a range from 0 to 100.
Every scale has 10 observations (one for each person).

What I would like to do is plot a line for each scale that represents the range (a line from 0 to 100) and on that line I want Excel to show dots (or something else) for every individual observation, and 1 dot for the average of all observations. This has to be repeated for every scale on a separate line.

Example:
Scale A
0|--x--x-----x----x--------x-------O--------x-------x----x-------x---x--|100

I'm guessing this should be possible in Excel, but I can't figure out how! Is there anyone who can help me with this? Thanks in advance!

 

[NARAYANK991]

Hi ,

Rather than use a chart , you can do this within the worksheet cells themselves ; if you can upload your file , it will be easier for others to help.

Narayan

 

[anke12]

Hello,

Thank you for replying.
I've added the (simplified) file. It would be very useful if you could show what you mean

Anke

 

[NARAYANK991]

Hi Anke ,

Can you clarify how you want the points plotted ?

Do the values down a column represent a line of points , or is it the values in a row which represent a line of points ?

Narayan

 

[anke12]

Hi Narayan,

The values down a column represent a line of points, with the last value representing the average of the other values, which are individual observations.

I would like to plot those points on a line, which also shows the range of values that are possible. E.g. for the first column of values in the example, the range is 20-120.

Anke

 

[NARAYANK991]

Hi Anke ,

Isn't it somewhat inconvenient to have the points in a horizontal line , when the data values themselves are in a column , arranged vertically ?

Can we not have the points also arranged horizontally ?

Secondly , I don't think there is a way to show two points with the same value , as for example 101. Is that a problem ?

Narayan

 

[anke12]

Hi Narayan,

Yes, rearranging them would be possible, as shown in the example attached.
Also, it's not a problem if two identical values are shown as 1 point.

Anke

 

[NARAYANK991]

Hi Anke ,

Great. Can you check back after an hour or so ?

Narayan

 

[anke12]

Yes, thank you!

 

[NARAYANK991]

Hi Anke ,

See this file ; is it acceptable ?

If you confirm that it is OK , I'll either do it for the remaining columns of data , or explain how exactly you can extend it.

Narayan

 

[anke12]

Thank you very much, I figured it out already with your example. Very helpful!

Anke