Subtotal Problem

[Salman]

I have data on a worksheet through functions and macros, postions of data
changes all the time, no a specific range can be define, i am known to
subtotal function but for now its not enough

i have two major fields for my data
first is sector and and is company

Column A Column B
Sector Company

i need subtotal Average for every company (same co. could be on multiple
rows) but the grand total Average for sector (Because average result for the
whole data and average result of subtotal of companies are entirely
different) i have 34 different sectors and around 250 companies and have to
average on 15 columns

i have made the data available already sorted through sector and then
company and have 2 blanks rows after each company through function and macro

can anyone help if this could be done through any funtion or macro, it would
be really great
thanks

 

[Toppers]

Look at SUMPRODUCT:

For average by Company within Sector

=SUMPRODUCT(--(A2:A100=Sector),--(B2:B100=Company),(C2:C100))/SUMPRODUCT(--(A2:A100=Sector),--(B2:B100=Company))

For average by Sector

=SUMPRODUCT(--(A2:A100=Sector,(C2:C100))/SUMPRODUCT(--(A2:A100=Sector))


where column C are values to be averaged

Replace Sector & company with cell addresses

HTH

 

[Salman]

thanks
but my problem is that data is called through macro and rows are not
specific for subtotals, i tried a macro through which i move on to some
extent but result are not accurate, i am not much familiar with macros, yes
can alter to some extent;

- sector average base on companies in that sector is not accurate
- how to handle #Div/0! error within that macro

below is the macro i am using

Sub test()
Range("a3470").Select
Selection.Subtotal GroupBy:=Array(1), Function:=xlAverage,
TotalList:=Array(7, 8, 9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=Array(2), Function:=xlAverage,
TotalList:=Array(7, 8, 9, 10, 11), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[Salman]

thanks
but my problem is that data is called through macro and rows are not
specific for subtotals, i tried a macro through which i move on to some
extent but result are not accurate, i am not much familiar with macros, yes
can alter to some extent;

- sector average base on companies in that sector is not accurate
- how to handle #Div/0! error within that macro

below is the macro i am using

Sub test()
Range("a3470").Select
Selection.Subtotal GroupBy:=Array(1), Function:=xlAverage,
TotalList:=Array(7, 8, 9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=Array(2), Function:=xlAverage,
TotalList:=Array(7, 8, 9, 10, 11), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End Sub