Please help with VB in calculation

CThai

Active Member
Joined
Mar 18, 2007
Messages
295
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi

What are you seeking help with? If it is the calculations for the week, month and year than can you describe the input values and the output values you want to see? Or maybe provide some sample data?

Andrew
 
Upvote 0
Hi Andrew

Thank you for your respond, I'm looking for the calculations for the, week, month and year...

Using the Capacity offline value - to calculate the overall averages of the week the product was offline.

for the week i have it using the week numbers - such as 1/2000 (week/year)

how can i provide you with a data sample? is there a way i can attach it or send it to you?

again thanks

Cthai
 
Upvote 0
Hi Andrew -

below is an example of the data... thank you
PADD1_test.xls
ABCDEFGHIJK
1Outage DateUnit IDOwner NamePlantIDPlant NameCapacity OfflineTypeUnit TypeWeekMonthYear
21-Apr-991001321Phillips1518597Bayway150000PlannedFCCU1430-Apr-991999
32-Apr-991001321Phillips1518597Bayway150000PlannedFCCU1430-Apr-991999
43-Apr-991001321Phillips1518597Bayway150000PlannedFCCU1430-Apr-991999
58-Apr-991001322Conoco1508498Savannah150000PlannedCCR1530-Apr-991999
69-Apr-991001322Conoco1508498Savannah150000PlannedCCR1530-Apr-991999
711-Apr-991001322Conoco1508498Savannah150000PlannedCCR1530-Apr-991999
812-Apr-991001322Conoco1508498Savannah150000PlannedCCR1630-Apr-991999
913-Apr-991001322Conoco1508498Savannah150000PlannedCCR1630-Apr-991999
1017-Apr-991005551Valero1752159Delaware150000PlannedAtmosphe1630-Apr-991999
1118-Apr-991005551Valero1752159Delaware150000PlannedAtmosphe1630-Apr-991999
1220-Apr-991005551Valero1752159Delaware150000PlannedAtmosphe1730-Apr-991999
1323-Apr-991005551Valero1752159Delaware150000PlannedAtmosphe1730-Apr-991999
1424-Apr-991005551Valero1752159Delaware800000PlannedAtmosphe1730-Apr-991999
1525-Apr-991006598Sun1013058Trainer75000PlannedSemireg1730-Apr-991999
1626-Apr-991006598Sun1013058Trainer35000PlannedSemireg1830-Apr-991999
1727-Apr-991006598Sun1013058Trainer150000PlannedSemireg1830-Apr-991999
1828-Apr-991006598Sun1013058Trainer150000PlannedSemireg1830-Apr-991999
1930-Apr-991006598Sun1013058Trainer150000PlannedSemireg1830-Apr-991999
2023-Jan-001001667Texa1008700Marcus87000UnplannedAtmosph431-Jan-002000
2124-Jan-001001667Texa1008700Marcus87000UnplannedAtmosph531-Jan-002000
2225-Jan-001001667Texa1008700Marcus87000UnplannedAtmosph531-Jan-002000
2326-Jan-001001667Texa1008700Marcus87000UnplannedAtmosph531-Jan-002000
2421-Feb-001000290Hess1502056Port60000Planned*******929-Feb-002000
2522-Feb-001000290Hess1502056Port60000Planned*******929-Feb-002000
2624-Feb-001000290Hess1502056Port60000Planned*******929-Feb-002000
2725-Feb-001000290Hess1502056Port60000Planned*******929-Feb-002000
2826-Feb-001000290Hess1502056Port60000Planned*******929-Feb-002000
Sheet1
 
Upvote 0
I dont know if this would be any help, but i did try a code to generate the pivot table... just wasnt able to get the calculation correctly...
 
Upvote 0
sorry - can anyone help me with this?

I would really appreciate any help or advise...
 
Upvote 0
Hi

I'm sorry but I don't understand what calculation you want to make. Given the data you displayed, what value would you want to see for the first row?

Andrew

just wasnt able to get the calculation correctly...
 
Upvote 0
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.
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
 
Upvote 0
Hi Andrew -

below is the codes that i use to create my pivot table - I'm unable to get the "week" to calculate i want to divide the total amount 7 (monday-sunday) and get the average/sum for the week for the "capacity offline"

I hope this make sense and i really appreciate you taken your time in helping me with this

Code:
Option Explicit

Sub PADDPivotTables()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim SummarySheet As Worksheet
    Dim ItemName As String
    Dim Row As Integer, i As Integer
    
    Application.ScreenUpdating = False
    
'   Delete Summary sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Summary").Delete
    On Error GoTo 0
    
'   Add Summary sheet
    Set SummarySheet = Worksheets.Add
    ActiveSheet.Name = "Summary"
    
'   Create Pivot Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Add( _
      SourceType:=xlDatabase, _
      SourceData:=Sheets("PADD1").Range("A1"). _
        CurrentRegion.Address)
    
    Row = 1
    For i = 1 To 14
        ItemName = Sheets("PADD1").Cells(1, i + 2)
'       Create pivot table
        Set PT = PTCache.CreatePivotTable _
            (TableDestination:=SummarySheet.Cells(Row, 1), _
             TableName:=ItemName)
        Row = Row + 11
        
'       Add the fields
        With PT.PivotFields(ItemName)
            .Orientation = xlDataField
            .Name = "week"
            .Calculation = xldivid
        
        End With
    
        With PT.PivotFields(ItemName)
            .Orientation = xlDataField
            .Name = "Pct"
            .Calculation = xlPercentOfTotal
        End With
    
        PT.AddFields RowFields:=Array(ItemName, "Data")
        PT.PivotFields("Unit Type").Orientation = xlColumnField
        PT.PivotFields("Type").Orientation = xlColumnField
     Next i
    
'   Adjust column widths
    Columns("A:G").EntireColumn.AutoFit
End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,215,530
Messages
6,125,350
Members
449,220
Latest member
Edwin_SVRZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top