Average help needed.

  • Thread starter Thread starter Dark Horse
  • Start date Start date
D

Dark Horse

I have a spreadsheet of what is basically names and numbers.
Most names have more than one entry, and each individual entry has a number
associated with it.
I want to average the numbers for each name and output the result to a
separate sheet with just the individual name and the average.
Can this be done, and if so, how?
 
You can use a Pivot Table to do this. Select your data, then go to the data
menu, choose Pivot Table, and follow the wizard steps.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
In that case I must be stupid, because I cannot get anything to work
anything like I expect it to or want it to.
Sometimes, it would help if you 'experts' assumed that those of us asking
the question are complete morons and gave the answer in simple
easy-to-follow steps.

First select what data, the name or the number?
There are other unwanted data rows between the two so I cannot select both
at once.
I want the names from column A with the numbers from column P.

However, once I have done what you ask I get Headings at various locations
across the sheet:
Drop Page Fields here
Drop Column Fields here
Drop Row Fields here
Drop Data Items here
and a box that says pivot table and doesn't seem to do anything at all.

To some of us beginners, wizards are not that wizard - as we don't know what
they do, and what we want them to do.
Someone else told me I need Macros, and what are they?

Can someone please help a novice?
 
Put headers in your worksheet--one row (use alt-enters to put multiple lines in
each cell).

Say your data is in A2:B9999
Your headers in A1:B1--named Names and Qty

Select your range (A1:B9999)
Then Data|Pivottable...
(different versions of excel have different captions)

Use excel list or database and pivottable if your version has these prompts
<next>

Since you selected the range to start
<next>

Click the layout button

Drag Names to the row box
drag qty to the data box
(if it doesn't say Sum of Qty, then double click on it and choose Sum)

Click Ok

New sheet is ok for me.
Click Finish

Your pivottable is done.

Be aware that if you change the raw data, your pivottable doesn't automatically
refresh. Just right click on that pivottable and click Refresh to update it.

===
Once you see how easy this is, you'll want to use it for more things. So create
some test data (more than 2 columns) and try it out.

You'll notice that the grey "button" like cells are hot. You can drag them
over, up, down, left right. Experiment. If it gets completely unusable, right
click and choose Wizard and get to the layout and fix it up the way you want.

And play some more.

(Or delete that pt sheet and do it again.

If you've ever struggled with Data|Subtotals, copied the visible cells to a new
sheet and did more processing, you'll love the pivottables.

Here are some links for pivottable info:

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
Oops. The subject says Average and I missed that part.

Change this:
(if it doesn't say Sum of Qty, then double click on it and choose Sum)
to
(double click on it and choose Average)

And if you double click on those grey buttons, you'll see lots of stuff. (and
right click on everything, too!)
 
I'm beginning to feel sorry that I asked this question at all, it just seems
to keep getting more and more difficult and complicated.
However, I get the definite impression that its my error for not explaining
the problem coherently enough.
I'll explain a couple of points that apply to what has just been suggested:

1. The computer with the spreadsheet does not have a printer installed, so
headers etc refuse to comply.
2. Data is not in consecutive columns. Names are in Column A, numbers are in
column P

This is just the way this has to be for data entry and calculation.
 
I didn't mean headers for when you print. I meant headers in row 1 to identify
each column.

This may seem difficult, but once you do it, you'll be amazed that it was so
easy.

(A hint: make sure you put headers in all the columns: A:P and then just use A
& P when you do the pivottable.)

Dark said:
I'm beginning to feel sorry that I asked this question at all, it just seems
to keep getting more and more difficult and complicated.
However, I get the definite impression that its my error for not explaining
the problem coherently enough.
I'll explain a couple of points that apply to what has just been suggested:

1. The computer with the spreadsheet does not have a printer installed, so
headers etc refuse to comply.
2. Data is not in consecutive columns. Names are in Column A, numbers are in
column P

This is just the way this has to be for data entry and calculation.
Click to expand...
 
The result?

A field in your source data has more unique items than can be used in a
pivot table. Microsoft Excel may not be able to create the Pivot Table, or
may create the Pivot Table without the data from this field.

Let's just forget the whole damn thing shall we.
There has to be a better and easier way than this.
 
How about sorting your data by name, then Data|subtotal. Use average as the
function.

Then use the outlining symbols to the left to hide the details (just show the
subtotals).

Then select that range
edit|goto special|visible cells only
copy
paste to new sheet.
 
Dark Horse said:
The result?

A field in your source data has more unique items than can be used in a
pivot table. Microsoft Excel may not be able to create the Pivot Table, or
may create the Pivot Table without the data from this field.
Click to expand...

It sounds like you tried to put in a column the stuff that should be in a
row . If you have lots of unique data, it needs to go down the rows because
Excel can only handle 256 columns (I think that is the right number), but
can handle 65,000 or so rows.

Here are the steps:
1 - You grab all of the data from cols B - col P and create the Pivot Table
using the Wizard and just hit the "Finish" button as soon as it shows up.
It will create the Pivot Table shell on a new worksheet. (you must select
all of the cells, but you only need to use the two fields you want and can
ignore the rest)
..
2 - When the Pivot Table shell shows up on the new sheet, drag the labels
(the names) to the Row field and drag the numbers to the Data field (that
big part in the middle). It should automatically sum the information by
the individual's name.

3 - Then you can double click on the gray bar that says "Sum of Amount" (it
should be in cell A3) and a dialog box will open. Change from "Sum" to
"Average" an hit OK. The result should be what you want.

Let's just forget the whole damn thing shall we.
There has to be a better and easier way than this.
Click to expand...

This is the easiest way :)
 
That didn't work either, and this is so complicated that I am just not
capable of understanding it, so can we all just forget it please?
All I wanted was a simple macro, but maybe that's not possible.
 
Let me summarise:
My data is spread across 26 columns, and down 25364 rows.
Column A is a list of names and column P is a list of scores/ratings that
those names have achieved on various dates.
Some names have just one entry, other names have as many as 20 entries.
There were a total of 6442 different names last time I counted.
I want to create a list with just 1 row and 2 columns for each name - giving
the name in question and an average of all scores achieved by that name.

I would ideally like the information to be output to a new, separate,
sheet - and I would like to be able perform this once a week.
Is it possible easily?
 
Did you try sorting, then data|subtotals, then copy|paste the visible cells?

If that worked, you could record a macro when you did it for real.

I did that and then modified my recorded macro to get this:

Option Explicit
Sub testme01()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim myRng As Range

Set curWks = Worksheets("sheet1")
If curWks.Parent.Saved = False Then
MsgBox "Please save your workbook. " _
& "This will destroy the original sort sequence"
Exit Sub
End If

Application.ScreenUpdating = False

Set newWks = Workbooks.Add(1).Worksheets(1) 'worksheet in a new workbook

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "P"))

Application.StatusBar = "Doing the sort: " & Now
myRng.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Application.StatusBar = "Doing the subtotal: " & Now
myRng.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(16), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

.Outline.ShowLevels RowLevels:=2

Application.StatusBar = "Copying the averages: " & Now
myRng.Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=newWks.Range("A1")

End With

With newWks
Application.StatusBar = "Formatting the summaries: " & Now
.Range("b:o").EntireColumn.Delete
.Cells.RemoveSubtotal
With .Range("a:a")
.Replace What:=" Average", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Font.Bold = False
End With
Set myRng = .UsedRange 'try to reset last cell
End With

With Application
.ScreenUpdating = True
.StatusBar = False
End With

MsgBox "Please close your original workbook without saving." & vbLf _
& "The original sort sequence was altered"

End Sub

I assumed that you had headers in row 1 and the last row with data had something
in column A.
 
I'll give it a go, but it looks complicated

Dave Peterson said:
Did you try sorting, then data|subtotals, then copy|paste the visible cells?

If that worked, you could record a macro when you did it for real.

I did that and then modified my recorded macro to get this:

Option Explicit
Sub testme01()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim myRng As Range

Set curWks = Worksheets("sheet1")
If curWks.Parent.Saved = False Then
MsgBox "Please save your workbook. " _
& "This will destroy the original sort sequence"
Exit Sub
End If

Application.ScreenUpdating = False

Set newWks = Workbooks.Add(1).Worksheets(1) 'worksheet in a new workbook

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "P"))

Application.StatusBar = "Doing the sort: " & Now
myRng.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Application.StatusBar = "Doing the subtotal: " & Now
myRng.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(16), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

.Outline.ShowLevels RowLevels:=2

Application.StatusBar = "Copying the averages: " & Now
myRng.Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=newWks.Range("A1")

End With

With newWks
Application.StatusBar = "Formatting the summaries: " & Now
.Range("b:o").EntireColumn.Delete
.Cells.RemoveSubtotal
With .Range("a:a")
.Replace What:=" Average", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Font.Bold = False
End With
Set myRng = .UsedRange 'try to reset last cell
End With

With Application
.ScreenUpdating = True
.StatusBar = False
End With

MsgBox "Please close your original workbook without saving." & vbLf _
& "The original sort sequence was altered"

End Sub

I assumed that you had headers in row 1 and the last row with data had something
in column A.
Click to expand...
 
I was right, and it also didn't do what I wanted, and it took absolutely
ages to work.
Thankfully, there is a better way:

Sub FilterNames()
Dim TYOSummary As Worksheet
Dim MaxRow As Integer

Application.ScreenUpdating = False ' switch off screen update
Application.DisplayAlerts = False ' switch off alerts

' Clear any existing summary data
Set TYOSummary = Sheets("Summary") ' << Change this if you change the
sheet name
TYOSummary.Cells.Clear

With Sheets("AW")

' Establish how many rows of data there are
MaxRow = .Range("A65536").End(xlUp).Row

' Filter the names
' the range is from Row 1 (heading) to the last row with a name in it
' the "filter to" range is on a new sheet (Summary)

.Range("A1:A" & MaxRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=TYOSummary.Range("A1"), _
Unique:=True
End With

With TYOSummary

' Find the end of the table
MaxRow = .Range("A65536").End(xlUp).Row

' Create the formula for obtaining the averages of the columns
.Range("B2").Formula = _
"=SUMIF('2YO'!A:A,A2,'2YO'!Q:Q)/COUNTIF('2YO'!A:A,A2)"

' Copy the formulae down
.Range(.Range("B2"), .Range("D" & MaxRow)).FillDown

' Put some headings in to Row 1 and format the headings and columns
.Range("A1") = Application.WorksheetFunction.Proper(.Range("A1"))
.Range("B1") = "Rating Average"
.Range("A1:D1").Font.Bold = True
.Columns("B").NumberFormat = "0"
.Columns("B").HorizontalAlignment = xlCenter
.Range("A:B").EntireColumn.AutoFit

Application.DisplayAlerts = True ' switch on screen update
Application.ScreenUpdating = True ' switch off alerts

End With

' Select the header line of the new table
TYOSummary.Activate ' activate the summary worksheet
Range("A1").Select ' select the top left cell

End Sub


That does exactly what I want on the can, and is much quicker and more
accurate - and no, it is not my own work.
Someone contacted me privately and did it for me.




Dark Horse said:
I'll give it a go, but it looks complicated

Dave Peterson said:
Did you try sorting, then data|subtotals, then copy|paste the visible cells?

If that worked, you could record a macro when you did it for real.

I did that and then modified my recorded macro to get this:

Option Explicit
Sub testme01()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim myRng As Range

Set curWks = Worksheets("sheet1")
If curWks.Parent.Saved = False Then
MsgBox "Please save your workbook. " _
& "This will destroy the original sort sequence"
Exit Sub
End If

Application.ScreenUpdating = False

Set newWks = Workbooks.Add(1).Worksheets(1) 'worksheet in a new workbook

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range(.Cells(FirstRow, "A"), .Cells(LastRow, "P"))

Application.StatusBar = "Doing the sort: " & Now
myRng.Sort Key1:=.Range("A2"), Order1:=xlAscending,
Click to expand...
Header:=xlYes,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Application.StatusBar = "Doing the subtotal: " & Now
myRng.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=Array(16), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

.Outline.ShowLevels RowLevels:=2

Application.StatusBar = "Copying the averages: " & Now
myRng.Cells.SpecialCells(xlCellTypeVisible).Copy _
Destination:=newWks.Range("A1")

End With

With newWks
Application.StatusBar = "Formatting the summaries: " & Now
.Range("b:o").EntireColumn.Delete
.Cells.RemoveSubtotal
With .Range("a:a")
.Replace What:=" Average", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Font.Bold = False
End With
Set myRng = .UsedRange 'try to reset last cell
End With

With Application
.ScreenUpdating = True
.StatusBar = False
End With

MsgBox "Please close your original workbook without saving." & vbLf _
& "The original sort sequence was altered"

End Sub

I assumed that you had headers in row 1 and the last row with data had something
in column A.
Click to expand...
average
Click to expand...
 
Back
Top