| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Average in pivot item
Hi friends,
I need to calculate the average of a column in a pivot for eg. Jan 09 and Feb 09 so on and so forth for the whole year. Now how can i calculate the average of count of Jan 09 and Feb 09 ie. (Jan 09 + Feb 09)/2 . Data in the column just says Jan 09, feb 09 , mar 09 so I have to take their count against other columns and then take its average. I tried calculating item in the pivot but its giving error "Calculated items do not work with customs subtotal". If you can advise the above or its alternative, I'll be greatful. Regards, KD |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Average in pivot item
Hi,
Please tell us how the data is structured in the source range -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi friends, I need to calculate the average of a column in a pivot for eg. Jan 09 and Feb 09 so on and so forth for the whole year. Now how can i calculate the average of count of Jan 09 and Feb 09 ie. (Jan 09 + Feb 09)/2 . Data in the column just says Jan 09, feb 09 , mar 09 so I have to take their count against other columns and then take its average. I tried calculating item in the pivot but its giving error "Calculated items do not work with customs subtotal". If you can advise the above or its alternative, I'll be greatful. Regards, KD |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Average in pivot item
Hi Ashish, Data is structured in one sheet in rows and columns. Columns being Region, Grade OHC etc. etc. For heading being OHC, Data in rows being Jan 09 Jan 09 Jan 09 Jan 09 Jan 09 Feb 09 Feb 09 Feb 09 Feb 09 Mar 09 I want to take take the average of months count. Is that possible through pivot, if not what alternative can you suggest. Thanks in adavnce for any help. KD "Ashish Mathur" wrote: Hi, Please tell us how the data is structured in the source range -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi friends, I need to calculate the average of a column in a pivot for eg. Jan 09 and Feb 09 so on and so forth for the whole year. Now how can i calculate the average of count of Jan 09 and Feb 09 ie. (Jan 09 + Feb 09)/2 . Data in the column just says Jan 09, feb 09 , mar 09 so I have to take their count against other columns and then take its average. I tried calculating item in the pivot but its giving error "Calculated items do not work with customs subtotal". If you can advise the above or its alternative, I'll be greatful. Regards, KD |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Average in pivot item
Hi,
DId you right click on any item in the pivot and change the summarise by setting (under Field settings) from sum/count to average -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi Ashish, Data is structured in one sheet in rows and columns. Columns being Region, Grade OHC etc. etc. For heading being OHC, Data in rows being Jan 09 Jan 09 Jan 09 Jan 09 Jan 09 Feb 09 Feb 09 Feb 09 Feb 09 Mar 09 I want to take take the average of months count. Is that possible through pivot, if not what alternative can you suggest. Thanks in adavnce for any help. KD "Ashish Mathur" wrote: Hi, Please tell us how the data is structured in the source range -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi friends, I need to calculate the average of a column in a pivot for eg. Jan 09 and Feb 09 so on and so forth for the whole year. Now how can i calculate the average of count of Jan 09 and Feb 09 ie. (Jan 09 + Feb 09)/2 . Data in the column just says Jan 09, feb 09 , mar 09 so I have to take their count against other columns and then take its average. I tried calculating item in the pivot but its giving error "Calculated items do not work with customs subtotal". If you can advise the above or its alternative, I'll be greatful. Regards, KD |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Average in pivot item
No, it is count only, if I average it, it will show #DIV/0! error.
"Ashish Mathur" wrote: Hi, DId you right click on any item in the pivot and change the summarise by setting (under Field settings) from sum/count to average -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi Ashish, Data is structured in one sheet in rows and columns. Columns being Region, Grade OHC etc. etc. For heading being OHC, Data in rows being Jan 09 Jan 09 Jan 09 Jan 09 Jan 09 Feb 09 Feb 09 Feb 09 Feb 09 Mar 09 I want to take take the average of months count. Is that possible through pivot, if not what alternative can you suggest. Thanks in adavnce for any help. KD "Ashish Mathur" wrote: Hi, Please tell us how the data is structured in the source range -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi friends, I need to calculate the average of a column in a pivot for eg. Jan 09 and Feb 09 so on and so forth for the whole year. Now how can i calculate the average of count of Jan 09 and Feb 09 ie. (Jan 09 + Feb 09)/2 . Data in the column just says Jan 09, feb 09 , mar 09 so I have to take their count against other columns and then take its average. I tried calculating item in the pivot but its giving error "Calculated items do not work with customs subtotal". If you can advise the above or its alternative, I'll be greatful. Regards, KD |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Average in pivot item
Hi,
I am not clear about your question. Please mail your question to me at ask(at)ashishmathur(dot)com. Thank you. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... No, it is count only, if I average it, it will show #DIV/0! error. "Ashish Mathur" wrote: Hi, DId you right click on any item in the pivot and change the summarise by setting (under Field settings) from sum/count to average -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi Ashish, Data is structured in one sheet in rows and columns. Columns being Region, Grade OHC etc. etc. For heading being OHC, Data in rows being Jan 09 Jan 09 Jan 09 Jan 09 Jan 09 Feb 09 Feb 09 Feb 09 Feb 09 Mar 09 I want to take take the average of months count. Is that possible through pivot, if not what alternative can you suggest. Thanks in adavnce for any help. KD "Ashish Mathur" wrote: Hi, Please tell us how the data is structured in the source range -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "kd" wrote in message ... Hi friends, I need to calculate the average of a column in a pivot for eg. Jan 09 and Feb 09 so on and so forth for the whole year. Now how can i calculate the average of count of Jan 09 and Feb 09 ie. (Jan 09 + Feb 09)/2 . Data in the column just says Jan 09, feb 09 , mar 09 so I have to take their count against other columns and then take its average. I tried calculating item in the pivot but its giving error "Calculated items do not work with customs subtotal". If you can advise the above or its alternative, I'll be greatful. Regards, KD |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculate Average in pivot item
Excel 2007 PivotTable
Count items, average count of items: http://www.mediafire.com/file/mghfmmzkxt3/11_30_09.xlsx http://c0444202.cdn.cloudfiles.racks.../11_30_09.xlsx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Time for Specific Item | Excel Discussion (Misc queries) | |||
| How do I calculate a weighted average in a pivot table? | Excel Discussion (Misc queries) | |||
| I want to us Pivot Table to calculate the % of one Item ("Yes") | Excel Worksheet Functions | |||
| Calculate Based on Drop Down Item Selected | Excel Worksheet Functions | |||
| Using a pivot to calculate an average w/o 0's | Excel Discussion (Misc queries) | |||
Linear Mode
