Please help with VB in calculation

[CThai]

Hi
I'm looking for some help in calculating the week, month and year average,

In column A i have the dates (01-Apr-99 thru 01-Jan-06)
column B = Unit ID
column C = Owner name
column D = Plant ID plant ID would be use as a Key ID, since plant ID is different
column E = Plant Name
column F = Capacity offline (this is what i need to use to calculate)
column G = Type
column H = Unit type
column I = week
column J = month
column K = Year

I would like to create a pivot table where i can select a Plant ID and it would show me the average of the week, month and year... and all the other information...

is there a way to create this?

Thank you

Cthai

 

[CThai]

I did it manually to calculate the weekly average ---

I hope this explain what I am looking for:

what i need is

anything in "week" 14/1999 to sum and get the average for "Capacity offline" for the weekly average...
Please let me know if this is something you can help me with or if you need more information..

thanks a bunch!


EDIT: I deleted your HTMLMaker shot as it was too big and blowing the page out of proportion - Can you try a smaller sample? Smitty

 

[SydneyGeek]

Your problem with the columns is that 52 weeks * 6 years exceeds the 256 column limit. Try filtering by year: place Year in the Page Field area, select one of the years, then refresh the PT. You should then see all weeks for that particular year.

Denis

 

[CThai]

sorry about the image -

here is a smaller one -

PADD1_test.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Outage Date	Unit ID	Owner Name	PlantID	Plant Name	Capacity Offline	Week	Month	Year		Week Refer	DAILY AVERAGE	WEEKLY AVERAGE
2	19-Mar-01	1001619	Sunno	1863502	Eagle	150000	12/2001	31-Mar-01	2001		12	150000	150000
3	20-Mar-01	1001619	Sunno	1863502	Eagle	150000	12/2001	31-Mar-01	2001		12	150000
4	21-Mar-01	1001619	Sunno	1863502	Eagle	150000	12/2001	31-Mar-01	2001		12	150000
5	22-Mar-01	1001619	Sunno	1863502	Eagle	150000	12/2001	31-Mar-01	2001		12	150000
6	23-Mar-01	1001619	Sunno	1863502	Eagle	150000	12/2001	31-Mar-01	2001		12	150000
7	24-Mar-01	1001619	Sunno	1863502	Eagle	150000	12/2001	31-Mar-01	2001		12	150000
8	25-Mar-01	1001619	Sunno	1863502	Eagle	150000	12/2001	31-Mar-01	2001		12	150000
9	26-Mar-01	1001619	Sunno	1863502	Eagle	150000	13/2001	31-Mar-01	2001		13	150000	150000
10	27-Mar-01	1001619	Sunno	1863502	Eagle	150000	13/2001	31-Mar-01	2001		13	150000
11	28-Mar-01	1001619	Sunno	1863502	Eagle	150000	13/2001	31-Mar-01	2001		13	150000
12	29-Mar-01	1001619	Sunno	1863502	Eagle	150000	13/2001	31-Mar-01	2001		13	150000
13	30-Mar-01	1001619	Sunno	1863502	Eagle	150000	13/2001	31-Mar-01	2001		13	150000
14	31-Mar-01	1001619	Ohio	1008348	Point	150000	13/2001	31-Mar-01	2001		13	150000
15	1-Apr-01	1001619	Ohio	1008348	Point	150000	13/2001	30-Apr-01	2001		13	150000
16	2-Apr-01	1001619	Ohio	1008348	Point	150000	14/2001	30-Apr-01	2001		14	150000	150000
17	3-Apr-01	1001619	Ohio	1008348	Point	150000	14/2001	30-Apr-01	2001		14	150000
18	4-Apr-01	1001619	Ohio	1008348	Point	150000	14/2001	30-Apr-01	2001		14	150000
19	5-Apr-01	1001619	Ohio	1008348	Point	150000	14/2001	30-Apr-01	2001		14	150000
20	6-Apr-01	1001619	Ohio	1008348	Point	150000	14/2001	30-Apr-01	2001		14	150000
21	7-Apr-01	1001619	Ohio	1008348	Point	150000	14/2001	30-Apr-01	2001		14	150000
PADD1_weekly

 

[CThai]

Hi SydneyGeek -

I added the year and it's not showing the weekly average - for each year. i manually input the weekly average and monthly average so that when i put it into the pivot table i will show....

 

[SydneyGeek]

I'm having trouble visualising your layout. Send me a PM, I'll reply with my email address, and you can send the file.

Denis

 

[tusharm]

A PivotTable is about as efficient as one can get in terms of worksheet space usage. If it doesn't fit then no solution you come up with will fit on a worksheet.

That said, are you sure you had the row and column fields as I indicated and not the other way around?

Hi tusharm

I try to follow your idea for the pivot table but when i click finish to run the table i get an error " the Pivot talbe will not fit on the sheet"...

The idea is - i want to be able to select the Unit type (i.e CCR) see the capacity offline, averge (week, monthly and year) as well as the owner name, type... etc...

I dont know if pivot table is the best way too go, since i have a huge data... i would take any suggestion... just need something that works

thank you

Cthai

 

[CThai]

hi tusharm

In a PivotTable, put the PlantID as the row field, the week, month, and year as column fields and the Capacity Offline as the data field. For the data field, change Excel's default choice of Sum to Average.

that is exactly how i have the table created...

is there a way i can use VB to generate the daily/weekly/monthly average? and show only some of the information that needed...

 

[tusharm]

If the number of Plants is "reasonable," put the PlantID as the column field and the others as row fields.

Alternatively, create one PT for each of the Month, Year, and Quarter.

And, as far as relying on VBA goes, I really don't understand how it would yield better results. If there isn't enough space to show the results there isn't enough space to show the results. I'm sorry, but VBA is not going to create additional columns out of thin air! And, if you are willing to see less amount of information through the use of VBA why not do the same with Excel-native tools?

hi tusharm

In a PivotTable, put the PlantID as the row field, the week, month, and year as column fields and the Capacity Offline as the data field. For the data field, change Excel's default choice of Sum to Average.

that is exactly how i have the table created...

is there a way i can use VB to generate the daily/weekly/monthly average? and show only some of the information that needed...

 

[CThai]

If the number of Plants is "reasonable," put the PlantID as the column field and the others as row fields.

Alternatively, create one PT for each of the Month, Year, and Quarter.

the report have about 7000+ entries ... here an example of the pivot table

pivot.xls
	A	B	C	D	E
3	Unit ID	(All)
4	Capacity Offline	(All)
5	Outage Date	(All)
6
7	PlantID	Data	Owner	Plant Name	Total
8	1028348	Sum of DAILY AVERAGE	Bmore	FEN	220000
9			Bmore Total		220000
10		Sum of WEEKLY AVERAGE	Bmore	FEN	31428.57143
11			Bmore Total		31428.57143
12		Count of MONTHLY AVERAGE	Bmore	FEN
13			Bmore Total
14	1028348 Sum of DAILY AVERAGE				220000
15	1028348 Sum of WEEKLY AVERAGE				31428.57143
16	1028348 Count of MONTHLY AVERAGE
Pivot

 

[CThai]

tusharm,

i'm going to create a different sheet for weekly/monthly average - and see if the errors still occurs... i know that pivot table should be able to give me what i'm looking for... i just need to get it right...