Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Problem
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Problem
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Problem
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("a3:p470").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 "Toppers" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtotal Problem
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("a3:p470").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 "Toppers" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal command problem | Excel Worksheet Functions | |||
Subtotal problem. Very strange! | Excel Worksheet Functions | |||
Subtotal is still problem is 2003 SP2 | Excel Discussion (Misc queries) | |||
Excel Subtotal problem | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |