Getting Average by Year in Excel

[mmr3b9]

I have data like this below. I am using Excel 2000.


Year Month 210000 236000 237000
1990 1 2 341 188
1990 2 0 325 169
1990 3 1 343 174
1990 4 3 321 263
1990 5 1 354 305
1990 6 1 375 375
1990 7 2 415 211
1990 8 2 441 227
1990 9 5 430 205
1990 10 3 425 205
1990 11 2 420 198
1990 12 2 419 183
1991 1 2 367 136
1991 2 2 372 118
1991 3 2 397 170
1991 4 0 452 294
1991 5 2 488 359
1991 6 2 491 405
1991 7 2 514 401
1991 8 2 519 401
1991 9 2 511 370
1991 10 6 502 376
1991 11 6 460 324
1991 12 6 420 234


I need to get it to look like this with the average anuual number in the spaces below.


Year 210000 236000 237000
1990
1991


If someone can give me a way to go about this I would extremly appreciate it.

Thanks,
Mike

 

[SkipVought]

Mike,

I did this with a PivotTable in 5 minutes.

Sum of Value	Category			
Date     210000    236000    237000   Grand Total
1990         24   2520000      2703   2522727
1991         34   2520000      3588   2523622
Grand Total  58   5040000      6291   5046349

Here's what I did.

1. convert year and month to an Excel date in a new column, G

=date(A2,B2,1)

2. copy columns C - E to column H

3. in the new table start the PivotTable Wizard -- select the Multiple consolidation ranges option button -- NEXT -- I will create the page fields option button -- NEXT -- with the cursor in the Range textbox, select the new table and ADD -- NEXT --

Open the LAYOUT and drag the ROW and COLUMN buttons off the layout area -- OK -- FINISH

You will see a 4-cell pivottable. DOUBLE CLICK the bottom right cell.

What you get should look like this

Row       Column  Value
1/1/1990  210000        2
1/1/1990  236000   210000
1/1/1990  237000      188
2/1/1990  210000        0
2/1/1990  236000   210000
2/1/1990  237000      169

Change the Columns headings to whatever you like. I used Date, Category, Value

You now have what is referred to as a table with Normalized data or a Normalized table -- much easier to analyze and report on.

Start the PivotTable Wizard in this table.

Put Date in the Row area, Category in the Column are and Sum of Value in the Data area. -- FINISH

BUT...

you have distince dates and not years. NOT TO WORRY!

Right click the Date heading in the PivotTable and select Group & Outline -- Select year and finish

VOLA!


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mmr3b9]

Thanks skip for the response. It was interesting to try it out using a pivot table, but what i was looking for and should have specified is that I need to do this with code.

Cuz i need to run it a lot of times. So if anyone can help me out with some code on how to convert the numbers into annual averages i would appreciate it.

Thanks,
Mike

 

[SkipVought]

...
and why could this approach NOT be the basis for ongoing processing? Use the macro recorder as one of your tools.

The problem is with your data structure. It is NOT a "best practice" for an IT professional to maintain and use non-normalized data.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mmr3b9]

Hi,

I tried the pivot table the way you specified but it does not show up. my data actually has 170 columns and 145 rows. i just put a little sample on here.

So im just saying that i tried the pivot table and it did not work out for me. so if you or anyone has any other ideas about how i can go about this i would appreciate it.

Thanks for the help,
Mike

 

[SkipVought]

Doesn't matter that it has 170 columns. If you follow the instructions, it will work as long as each of your 170 column headings has a UNIQUE value.

How far were you able to get?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[mmr3b9]

I followed ur steps and then i click finish and the pivot table does not appear.

All the column headings are unique.
what do u suggest?

Thanks,
Mike

 

[KenWright]

For the data structure you have you could also do this in a heartbeat with Data / Subtotals and choose Average. You could record a macro whilst doing it if you really want it in code and it will give you all your averages / sums / counts / whatever you want.

Caveat is that the first column must be sorted so that all like years are together, which your example would indicate they already are.

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[mmr3b9]

Thanks KenWright for the response....can you give me an example of how u would use caveat in this situation. My excel help isnt too good. it doesnt have caveat in it.

Thanks,
mike

 

[SkipVought]

Mike,

Just do it (SUBTOTAL WIZARD), making sure that the YEAR column is sorted.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

LOL - Either do it manually as Skip has said using the Subtotal wizard, or if you want to do it with vba, and the data range will change each time, then try the following:-

Sub GetSubTots()

Dim lrow As Long
Dim lcol As Long
Dim i As Integer
Dim MyArray() As Integer

lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

ReDim MyArray(2 To lcol)
For i = 2 To lcol
MyArray(i) = i
Next i

With Range(Cells(1, 1), Cells(lrow, lcol))
.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=MyArray, Replace:=True, _
PageBreaks:=False, SummaryBelowData:=True
End With

End Sub

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

And if you want all your Averages highlighted, and collapsed, then try:-

Sub GetSubTotals()

Dim lrow As Long
Dim lcol As Long
Dim i As Integer
Dim MyArray() As Integer

lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

ReDim MyArray(2 To lcol)
For i = 2 To lcol
MyArray(i) = i
Next i


With Range(Cells(1, 1), Cells(lrow, lcol))
.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=MyArray, Replace:=True, _
PageBreaks:=False, SummaryBelowData:=True

'Have to reset this as you have now increased the rows
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Range("A1&quot.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($A1,7)=""Average"""
With .FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Interior.ColorIndex = 19
End With
End With
ActiveSheet.Outline.ShowLevels RowLevels:=2

End Sub

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[mmr3b9]

Thanks a lot guys. You both were very helpful.

 

[SkipVought]

mmr3b9

Looks to me like Ken was most helpful to you. Don't forget to Thank KenWright for this valuable post!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884