0

I want to make a graph like this, but I can't seem to make it correctly:

Graph Example

Data: 4, 2, 3, 4, 1, 3, 3, 2, 3, 4, 2, 3, 2, 1

Three elements: Y-axis potential values (1-4 or worst, below average, above average, best), Bar data-actual values, X-axis # any particular value (see below)

  • 4's/Best=3 total
  • 3's/Above-Average=5 total
  • 2's/Below-Average=4 total
  • 1's/Worst=2 total

I fairly new to Excel graphs, but not to Excel by any means. I tend to make things more complex then they are, but it seems Excel is making it more complex this time. It may help if you could submit an Excel file showing as I know the building of graphs has changed in various versions & steps might not suffice

2

Type the data into Excel or LibreOffice in two columns like this:

Score             Count
4's/Best          3
3's/Above-Average 5
2's/Below-Average 4
1's/Worst         2

Select all of it, click the "Chart" -icon in the icon filled toolbox (at the top of the window).

It really shouldn't be more than so -- you will get a dialog for setting options, but a simple click on [OK] should leave you with the graph, rotated 90 degree CCW though ;-)


Data    4   2   3   4   1   3   3   2   3   4   2   3   2   1

Score   Count                                                   
1       2                                                   
2       4                                                   
3       5                                                   
4       3                                                   

For each of the numbers under "Count" you need one =COUNTIF() which should be =COUNTIF($B$1:$O$1,"="&A3) for Score 1 if the "Data" word is in cell A1 and the rest typed in as shown in the table above.

2
  • This is getting me in the right area. Trouble is the raw data is spread over multiple sheets. Once I get those raw numbers say into 14 rows, do you know a excel formula to count the number of 4's, 3's, etc?
    – gregg
    Jul 18 '14 at 19:59
  • Look up COUNTIF() in the help, that is one way to count them.
    – Hannu
    Jul 18 '14 at 20:07
1

You say your data is in multiple sheets, so this may not apply, but if all the data points were in a single table/sheet, this would be a very simple task for a pivot chart.

If this is your data:

Student | Grade
Mike    | 1
Matt    | 3
Macy    | 4
Mark    | 4
Mary    | 1
Mutt    | 2 

A pivot table would like this

Grade    | Count of Students
1        | 2
2        | 1
3        | 1
4        | 2

And the chart should come out pretty well like you want.

2
  • Nice, ;-) I have always played with much more complex data when attempting pivot tables ;-)
    – Hannu
    Jul 18 '14 at 20:26
  • @Hannu - Good, then you can help with the question I am about to post, but it is more about pivot charts than tables. Jul 18 '14 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.