| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weekly average in pivot
I'd neet to get weekly averages in a Pivot Table. The source data may contain
several lines for one week. How can I get AVG weekly volume/customer/quarter from this kind of data? Any advice is greatly appreciated! Customer Volume Origin Year Q Month Week XXX 3000 A 2007 1 1 1 YYY 1000 A 2007 1 1 1 ZZZ 150 A 2007 1 1 1 XXX 350 A 2007 1 1 2 XXX 4000 L 2007 1 1 2 YYY 100 A 2007 1 1 2 YYY 800 L 2007 1 1 2 ZZZ 100 A 2007 1 1 2 ZZZ 150 L 2007 1 1 2 XXX 1000 A 2007 1 1 3 XXX 3000 L 2007 1 1 3 XXX 150 T 2007 1 1 3 YYY 500 A 2007 1 1 3 YYY 400 L 2007 1 1 3 ZZZ 200 L 2007 1 1 3 XXX 3500 A 2007 1 1 4 XXX 700 L 2007 1 1 4 YYY 1000 A 2007 1 1 4 YYY 500 L 2007 1 1 4 ZZZ 200 A 2007 1 1 4 XXX 3500 A 2007 1 2 5 XXX 300 L 2007 1 2 5 YYY 300 A 2007 1 2 5 YYY 1800 L 2007 1 2 5 ZZZ 50 A 2007 1 1 5 ZZZ 150 L 2007 1 1 5 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weekly average in pivot
Hi Max
In the PT Drag Customer to Row area Drag Q to Row area Drag Week to Row area Double click on these fields and set Subtotal to None Drag Volume to Data area Double click on Volume and change Sum to Average -- Regards Roger Govier "Max Stenbäck" wrote in message ... I'd neet to get weekly averages in a Pivot Table. The source data may contain several lines for one week. How can I get AVG weekly volume/customer/quarter from this kind of data? Any advice is greatly appreciated! Customer Volume Origin Year Q Month Week XXX 3000 A 2007 1 1 1 YYY 1000 A 2007 1 1 1 ZZZ 150 A 2007 1 1 1 XXX 350 A 2007 1 1 2 XXX 4000 L 2007 1 1 2 YYY 100 A 2007 1 1 2 YYY 800 L 2007 1 1 2 ZZZ 100 A 2007 1 1 2 ZZZ 150 L 2007 1 1 2 XXX 1000 A 2007 1 1 3 XXX 3000 L 2007 1 1 3 XXX 150 T 2007 1 1 3 YYY 500 A 2007 1 1 3 YYY 400 L 2007 1 1 3 ZZZ 200 L 2007 1 1 3 XXX 3500 A 2007 1 1 4 XXX 700 L 2007 1 1 4 YYY 1000 A 2007 1 1 4 YYY 500 L 2007 1 1 4 ZZZ 200 A 2007 1 1 4 XXX 3500 A 2007 1 2 5 XXX 300 L 2007 1 2 5 YYY 300 A 2007 1 2 5 YYY 1800 L 2007 1 2 5 ZZZ 50 A 2007 1 1 5 ZZZ 150 L 2007 1 1 5 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weekly average in pivot
Thank's Roger,
Unfortunately I wasn't looking for this, it returns the average of the individual cells. What I need is an average of the weekly volumes for each month/quarter/year (regardless of origin in the example). -Max- "Roger Govier" wrote: Hi Max In the PT Drag Customer to Row area Drag Q to Row area Drag Week to Row area Double click on these fields and set Subtotal to None Drag Volume to Data area Double click on Volume and change Sum to Average -- Regards Roger Govier "Max Stenbäck" wrote in message ... I'd neet to get weekly averages in a Pivot Table. The source data may contain several lines for one week. How can I get AVG weekly volume/customer/quarter from this kind of data? Any advice is greatly appreciated! Customer Volume Origin Year Q Month Week XXX 3000 A 2007 1 1 1 YYY 1000 A 2007 1 1 1 ZZZ 150 A 2007 1 1 1 XXX 350 A 2007 1 1 2 XXX 4000 L 2007 1 1 2 YYY 100 A 2007 1 1 2 YYY 800 L 2007 1 1 2 ZZZ 100 A 2007 1 1 2 ZZZ 150 L 2007 1 1 2 XXX 1000 A 2007 1 1 3 XXX 3000 L 2007 1 1 3 XXX 150 T 2007 1 1 3 YYY 500 A 2007 1 1 3 YYY 400 L 2007 1 1 3 ZZZ 200 L 2007 1 1 3 XXX 3500 A 2007 1 1 4 XXX 700 L 2007 1 1 4 YYY 1000 A 2007 1 1 4 YYY 500 L 2007 1 1 4 ZZZ 200 A 2007 1 1 4 XXX 3500 A 2007 1 2 5 XXX 300 L 2007 1 2 5 YYY 300 A 2007 1 2 5 YYY 1800 L 2007 1 2 5 ZZZ 50 A 2007 1 1 5 ZZZ 150 L 2007 1 1 5 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weekly average in pivot
Hi Max
I don't understand. With your data, I get a value of 1383.33 for Customer XXX, Q1, Wk3. This is the Average of 150 from Origin T, 3000 from Origin L and 1000 from Origin A Perhaps you could explain what you are wanting in more detail. -- Regards Roger Govier "Max Stenbäck" wrote in message ... Thank's Roger, Unfortunately I wasn't looking for this, it returns the average of the individual cells. What I need is an average of the weekly volumes for each month/quarter/year (regardless of origin in the example). -Max- "Roger Govier" wrote: Hi Max In the PT Drag Customer to Row area Drag Q to Row area Drag Week to Row area Double click on these fields and set Subtotal to None Drag Volume to Data area Double click on Volume and change Sum to Average -- Regards Roger Govier "Max Stenbäck" wrote in message ... I'd neet to get weekly averages in a Pivot Table. The source data may contain several lines for one week. How can I get AVG weekly volume/customer/quarter from this kind of data? Any advice is greatly appreciated! Customer Volume Origin Year Q Month Week XXX 3000 A 2007 1 1 1 YYY 1000 A 2007 1 1 1 ZZZ 150 A 2007 1 1 1 XXX 350 A 2007 1 1 2 XXX 4000 L 2007 1 1 2 YYY 100 A 2007 1 1 2 YYY 800 L 2007 1 1 2 ZZZ 100 A 2007 1 1 2 ZZZ 150 L 2007 1 1 2 XXX 1000 A 2007 1 1 3 XXX 3000 L 2007 1 1 3 XXX 150 T 2007 1 1 3 YYY 500 A 2007 1 1 3 YYY 400 L 2007 1 1 3 ZZZ 200 L 2007 1 1 3 XXX 3500 A 2007 1 1 4 XXX 700 L 2007 1 1 4 YYY 1000 A 2007 1 1 4 YYY 500 L 2007 1 1 4 ZZZ 200 A 2007 1 1 4 XXX 3500 A 2007 1 2 5 XXX 300 L 2007 1 2 5 YYY 300 A 2007 1 2 5 YYY 1800 L 2007 1 2 5 ZZZ 50 A 2007 1 1 5 ZZZ 150 L 2007 1 1 5 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weekly average in pivot
Hi Roger,
Let me clarify. I'm looking for the customer's volume per week. For the week it's easy, it's just the aggregated sum of all origins. Then I need to have the customer's average for the month (and quarter and year). In this report I don't care about the origin at all, it's just the way the data is recorded in my source database (and the cause of my current problem). Would it be a solution to use the firdt PT with sum fo volume as source for a second PT with avg of the fist PT. Can I define a PT as source for another PT? -Max- "Roger Govier" wrote: Hi Max I don't understand. With your data, I get a value of 1383.33 for Customer XXX, Q1, Wk3. This is the Average of 150 from Origin T, 3000 from Origin L and 1000 from Origin A Perhaps you could explain what you are wanting in more detail. -- Regards Roger Govier "Max Stenbäck" wrote in message ... Thank's Roger, Unfortunately I wasn't looking for this, it returns the average of the individual cells. What I need is an average of the weekly volumes for each month/quarter/year (regardless of origin in the example). -Max- "Roger Govier" wrote: Hi Max In the PT Drag Customer to Row area Drag Q to Row area Drag Week to Row area Double click on these fields and set Subtotal to None Drag Volume to Data area Double click on Volume and change Sum to Average -- Regards Roger Govier "Max Stenbäck" wrote in message ... I'd neet to get weekly averages in a Pivot Table. The source data may contain several lines for one week. How can I get AVG weekly volume/customer/quarter from this kind of data? Any advice is greatly appreciated! Customer Volume Origin Year Q Month Week XXX 3000 A 2007 1 1 1 YYY 1000 A 2007 1 1 1 ZZZ 150 A 2007 1 1 1 XXX 350 A 2007 1 1 2 XXX 4000 L 2007 1 1 2 YYY 100 A 2007 1 1 2 YYY 800 L 2007 1 1 2 ZZZ 100 A 2007 1 1 2 ZZZ 150 L 2007 1 1 2 XXX 1000 A 2007 1 1 3 XXX 3000 L 2007 1 1 3 XXX 150 T 2007 1 1 3 YYY 500 A 2007 1 1 3 YYY 400 L 2007 1 1 3 ZZZ 200 L 2007 1 1 3 XXX 3500 A 2007 1 1 4 XXX 700 L 2007 1 1 4 YYY 1000 A 2007 1 1 4 YYY 500 L 2007 1 1 4 ZZZ 200 A 2007 1 1 4 XXX 3500 A 2007 1 2 5 XXX 300 L 2007 1 2 5 YYY 300 A 2007 1 2 5 YYY 1800 L 2007 1 2 5 ZZZ 50 A 2007 1 1 5 ZZZ 150 L 2007 1 1 5 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weekly average in pivot
Hi Max
Double click on the Quarter field SubtotalsCustomaverage. Do the same for the Annual field. -- Regards Roger Govier "Max Stenbäck" wrote in message ... Hi Roger, Let me clarify. I'm looking for the customer's volume per week. For the week it's easy, it's just the aggregated sum of all origins. Then I need to have the customer's average for the month (and quarter and year). In this report I don't care about the origin at all, it's just the way the data is recorded in my source database (and the cause of my current problem). Would it be a solution to use the firdt PT with sum fo volume as source for a second PT with avg of the fist PT. Can I define a PT as source for another PT? -Max- "Roger Govier" wrote: Hi Max I don't understand. With your data, I get a value of 1383.33 for Customer XXX, Q1, Wk3. This is the Average of 150 from Origin T, 3000 from Origin L and 1000 from Origin A Perhaps you could explain what you are wanting in more detail. -- Regards Roger Govier "Max Stenbäck" wrote in message ... Thank's Roger, Unfortunately I wasn't looking for this, it returns the average of the individual cells. What I need is an average of the weekly volumes for each month/quarter/year (regardless of origin in the example). -Max- "Roger Govier" wrote: Hi Max In the PT Drag Customer to Row area Drag Q to Row area Drag Week to Row area Double click on these fields and set Subtotal to None Drag Volume to Data area Double click on Volume and change Sum to Average -- Regards Roger Govier "Max Stenbäck" wrote in message ... I'd neet to get weekly averages in a Pivot Table. The source data may contain several lines for one week. How can I get AVG weekly volume/customer/quarter from this kind of data? Any advice is greatly appreciated! Customer Volume Origin Year Q Month Week XXX 3000 A 2007 1 1 1 YYY 1000 A 2007 1 1 1 ZZZ 150 A 2007 1 1 1 XXX 350 A 2007 1 1 2 XXX 4000 L 2007 1 1 2 YYY 100 A 2007 1 1 2 YYY 800 L 2007 1 1 2 ZZZ 100 A 2007 1 1 2 ZZZ 150 L 2007 1 1 2 XXX 1000 A 2007 1 1 3 XXX 3000 L 2007 1 1 3 XXX 150 T 2007 1 1 3 YYY 500 A 2007 1 1 3 YYY 400 L 2007 1 1 3 ZZZ 200 L 2007 1 1 3 XXX 3500 A 2007 1 1 4 XXX 700 L 2007 1 1 4 YYY 1000 A 2007 1 1 4 YYY 500 L 2007 1 1 4 ZZZ 200 A 2007 1 1 4 XXX 3500 A 2007 1 2 5 XXX 300 L 2007 1 2 5 YYY 300 A 2007 1 2 5 YYY 1800 L 2007 1 2 5 ZZZ 50 A 2007 1 1 5 ZZZ 150 L 2007 1 1 5 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weekly average in pivot
Thank's again Roger,
I appreciate your attention to my problem. Unfortunately the average function always seems to calculate the average of all the underlying data cells. It's dividing the total by the number of data cells when I'd want to force number of weeks to be the divider. -Max- "Roger Govier" wrote: Hi Max Double click on the Quarter field SubtotalsCustomaverage. Do the same for the Annual field. -- Regards Roger Govier "Max Stenbäck" wrote in message ... Hi Roger, Let me clarify. I'm looking for the customer's volume per week. For the week it's easy, it's just the aggregated sum of all origins. Then I need to have the customer's average for the month (and quarter and year). In this report I don't care about the origin at all, it's just the way the data is recorded in my source database (and the cause of my current problem). Would it be a solution to use the firdt PT with sum fo volume as source for a second PT with avg of the fist PT. Can I define a PT as source for another PT? -Max- "Roger Govier" wrote: Hi Max I don't understand. With your data, I get a value of 1383.33 for Customer XXX, Q1, Wk3. This is the Average of 150 from Origin T, 3000 from Origin L and 1000 from Origin A Perhaps you could explain what you are wanting in more detail. -- Regards Roger Govier "Max Stenbäck" wrote in message ... Thank's Roger, Unfortunately I wasn't looking for this, it returns the average of the individual cells. What I need is an average of the weekly volumes for each month/quarter/year (regardless of origin in the example). -Max- "Roger Govier" wrote: Hi Max In the PT Drag Customer to Row area Drag Q to Row area Drag Week to Row area Double click on these fields and set Subtotal to None Drag Volume to Data area Double click on Volume and change Sum to Average -- Regards Roger Govier "Max Stenbäck" wrote in message ... I'd neet to get weekly averages in a Pivot Table. The source data may contain several lines for one week. How can I get AVG weekly volume/customer/quarter from this kind of data? Any advice is greatly appreciated! Customer Volume Origin Year Q Month Week XXX 3000 A 2007 1 1 1 YYY 1000 A 2007 1 1 1 ZZZ 150 A 2007 1 1 1 XXX 350 A 2007 1 1 2 XXX 4000 L 2007 1 1 2 YYY 100 A 2007 1 1 2 YYY 800 L 2007 1 1 2 ZZZ 100 A 2007 1 1 2 ZZZ 150 L 2007 1 1 2 XXX 1000 A 2007 1 1 3 XXX 3000 L 2007 1 1 3 XXX 150 T 2007 1 1 3 YYY 500 A 2007 1 1 3 YYY 400 L 2007 1 1 3 ZZZ 200 L 2007 1 1 3 XXX 3500 A 2007 1 1 4 XXX 700 L 2007 1 1 4 YYY 1000 A 2007 1 1 4 YYY 500 L 2007 1 1 4 ZZZ 200 A 2007 1 1 4 XXX 3500 A 2007 1 2 5 XXX 300 L 2007 1 2 5 YYY 300 A 2007 1 2 5 YYY 1800 L 2007 1 2 5 ZZZ 50 A 2007 1 1 5 ZZZ 150 L 2007 1 1 5 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
| with weekly score sheet how do I column a weekly progressive aver. | Excel Worksheet Functions | |||
| How to calculate the cumulative weekly average over several wksht | Excel Discussion (Misc queries) | |||
| How to Get Sum of the Average in Pivot Table? | Excel Discussion (Misc queries) | |||
Linear Mode
