Grouping Results by State

[Selden McCabe]

I have a spreadsheet with data like this which shows information on
applicants, sorted by what state they come from:

Name State UdergradGPA
Joe CA 3.2
Bill CA 2.7
Mary CA 3.3
Fred IL 3.3
Dan IL 3.2
Jan FL 3.5
Guy FL 3.4

etc.

I have sorted by state.

How can I do things like compute the totals for each state, and the average
UndergradGPA by state?

I tried the histogram from the analysis pack, but it didn't like the
non-numberic values (CA, IL, etc.) in the bin range.

Thanks,
---Selden McCabe

 

[Peo Sjoblom]

There are many ways to do this, pivot table with pivot chart would be one

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

in the layout you can drag the states to the row and the GPA to the data,
you will get
totals per state, a quick right click and you change that to averages..

Other ways would be using formulas like


=SUMIF(B:B,F2,C:C)

where B = states, C = GPA and F2 holds the state abbreviation , the above
will sum grouped by states


=SUMIF(B:B,F2,C:C)/COUNTIF(B:B,F2)

average

 

[Debra Dalgleish]

To summarize the results by state, you could use a PivotTable:

Select a cell in the list
Choose Data>PivotTable and PivotChart report
Click Next, select the cells with data, click Next
Click Layout
Drag the State button to the Row area
Drag the Name button to the Data area, where it will become
Count of Name
Drag the UndergradGPA button to the Data area
Double-click the UndergradGPA button, and choose to summarize
by Average
Click OK, choose a location for the Pivot Table, click Finish
To display the data fields horizontally, drag the Data button
onto the cell that contains the word Total

 

[Dave Peterson]

Since the data is already sorted, you could use data|subtotals.

But you may even want to look at Data|pivottable...

You can create some very nice summaries with not too much effort.

 

[Selden McCabe]

Thank you for your instructions. This has been very helpful, but I'm
missing something very basic from the concept of pivot tables!

When I try the instructions you gave me, I get %DIV/0! for all the
averages...

My original data looks like this:
StudentID State GPA
14468 AZ 3.02
18530 CA 2.95
18754 CA 2.89
15293 CA 3.77
15731 FL 2.69
16341 IL 3.87
17919 NJ 3.14


My pivot table (after following your instructions) looks like this:

State Data Total
AZ Count of StudentID 1
Average of GPA #DIV/0!
CA Count of StudentID 3
Average of GPA #DIV/0!
FL Count of StudentID 1
Average of GPA #DIV/0!
IL Count of StudentID 1
Average of GPA #DIV/0!
NJ Count of StudentID 1
Average of GPA #DIV/0!
Total Count of StudentID 7
Total Average of GPA #DIV/0!


I'm obviously doing something wrong, but I can't seem to figure out what!
I've already pulled out most of my hair trying to figure this out <g>

Thanks,
---Selden McCabe

 

[Selden McCabe]

Maybe this is a clue:
If I choose "Sum" instead of "Average" for the GPA field, the pivot table
contains 0 for all the GPA sums.
Obviously, it's not seeing the data...

 

[Debra Dalgleish]

Excel is treating your numbers as text, instead of numbers. To fix this:

Select a blank cell
Choose Edit>Copy
Select the cells that contain the GPA numbers
Choose Edit>Paste Special
Select Add
Click OK
To apply number formatting, choose Format>Cells
On the Number tab, select the appropriate format, then click OK.

There are other methods here for converting text to numbers:

http://www.contextures.com/xlDataEntry03.html