| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want average to change as per the filter is added in the sheet
I have a Sheet in which i have 12 columns containing text Values, and 4
columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want average to change as per the filter is added in the sheet
=subtotal(101,range)
That will calculate average over just the rows that are displayed - i.e, it ignores hidden rows "icsonu" wrote: I have a Sheet in which i have 12 columns containing text Values, and 4 columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want average to change as per the filter is added in the she
That means
=SUBTOTAL(101, M5:M5292) will SUM the visible rows. and =SUBTOTAL(101, M5:M5292) will AVERAGE them. And what is 101 in the subtotal option "Duke Carey" wrote: =subtotal(101,range) That will calculate average over just the rows that are displayed - i.e, it ignores hidden rows "icsonu" wrote: I have a Sheet in which i have 12 columns containing text Values, and 4 columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want average to change as per the filter is added in the she
Maybe the 101 is available only in 2007, but in XL2007 it limits the
operation to VISIBLE cells. If you want to SUM them, use 109 as the parameter. =SUBTOTAL(109,m5:m5292) Look in the Help file - you'll see that there's a bunch of operations that SUBTOTAL can handle 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP "icsonu" wrote: That means =SUBTOTAL(101, M5:M5292) will SUM the visible rows. and =SUBTOTAL(101, M5:M5292) will AVERAGE them. And what is 101 in the subtotal option "Duke Carey" wrote: =subtotal(101,range) That will calculate average over just the rows that are displayed - i.e, it ignores hidden rows "icsonu" wrote: I have a Sheet in which i have 12 columns containing text Values, and 4 columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want average to change as per the filter is added in the she
The 100+ range came in in Excel 2003.
-- __________________________________ HTH Bob "Duke Carey" wrote in message ... Maybe the 101 is available only in 2007, but in XL2007 it limits the operation to VISIBLE cells. If you want to SUM them, use 109 as the parameter. =SUBTOTAL(109,m5:m5292) Look in the Help file - you'll see that there's a bunch of operations that SUBTOTAL can handle 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP "icsonu" wrote: That means =SUBTOTAL(101, M5:M5292) will SUM the visible rows. and =SUBTOTAL(101, M5:M5292) will AVERAGE them. And what is 101 in the subtotal option "Duke Carey" wrote: =subtotal(101,range) That will calculate average over just the rows that are displayed - i.e, it ignores hidden rows "icsonu" wrote: I have a Sheet in which i have 12 columns containing text Values, and 4 columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want average to change as per the filter is added in the she
Thank You very Much for the Solution.
Regards, icsonu "icsonu" wrote: That means =SUBTOTAL(101, M5:M5292) will SUM the visible rows. and =SUBTOTAL(101, M5:M5292) will AVERAGE them. And what is 101 in the subtotal option "Duke Carey" wrote: =subtotal(101,range) That will calculate average over just the rows that are displayed - i.e, it ignores hidden rows "icsonu" wrote: I have a Sheet in which i have 12 columns containing text Values, and 4 columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want average to change as per the filter is added in the she
Thank You very Much for the Solution.
Regards, icsonu "Duke Carey" wrote: Maybe the 101 is available only in 2007, but in XL2007 it limits the operation to VISIBLE cells. If you want to SUM them, use 109 as the parameter. =SUBTOTAL(109,m5:m5292) Look in the Help file - you'll see that there's a bunch of operations that SUBTOTAL can handle 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP "icsonu" wrote: That means =SUBTOTAL(101, M5:M5292) will SUM the visible rows. and =SUBTOTAL(101, M5:M5292) will AVERAGE them. And what is 101 in the subtotal option "Duke Carey" wrote: =subtotal(101,range) That will calculate average over just the rows that are displayed - i.e, it ignores hidden rows "icsonu" wrote: I have a Sheet in which i have 12 columns containing text Values, and 4 columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
I want average to change as per the filter is added in the she
Thanks Bob
"Bob Phillips" wrote: The 100+ range came in in Excel 2003. -- __________________________________ HTH Bob "Duke Carey" wrote in message ... Maybe the 101 is available only in 2007, but in XL2007 it limits the operation to VISIBLE cells. If you want to SUM them, use 109 as the parameter. =SUBTOTAL(109,m5:m5292) Look in the Help file - you'll see that there's a bunch of operations that SUBTOTAL can handle 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP "icsonu" wrote: That means =SUBTOTAL(101, M5:M5292) will SUM the visible rows. and =SUBTOTAL(101, M5:M5292) will AVERAGE them. And what is 101 in the subtotal option "Duke Carey" wrote: =subtotal(101,range) That will calculate average over just the rows that are displayed - i.e, it ignores hidden rows "icsonu" wrote: I have a Sheet in which i have 12 columns containing text Values, and 4 columns having numeric values. I have to calculate the average of the 4 columns of the numeric values. I want the average to be displayed as per I add the filter to the 12 text values columns randomly. Can U help me out. I am willing to send you the Sheet so you can have a better Idea. Regards icsonu |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filter not including added data | Excel Discussion (Misc queries) | |||
| automatically change the worksheet reference when new sheet added | Excel Worksheet Functions | |||
| Average only the last 3 cells in a row and self-update as added | Excel Worksheet Functions | |||
| seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
| repost: seeking help on how to automatically shift an average range as new data is added | Excel Discussion (Misc queries) | |||
Linear Mode
