pivot table divide help

[kidrobot]

If I have two fields for my data that are both SUM. Lets say SUM of cars and SUM of road. Is there any way that I can divide these for every record within the pivot table? So I want to know

SUM of cars/SUM of road


and have this populate maybe below each instance of the sums. If this is confusing let me know.

 

[shades]

Howdy. Can you provide a small sample of dummy data, and then a mockup of what you want?
________
Jailbroken

 

[kidrobot]

Attached.

Right under

Sum of CountOfeqinit
Sum of SumOfLength

would it be possible to add a row that has them divided?

 

[shades]

So, in your example, you want E5/E6 (81/2385)?
________
Hawaii Marijuana Dispensary

 

[kidrobot]

well E6/E5 sorry for flipping it around.

 

[shades]

Yes, that can be done. Select cell E6, then on the Pivot Table toolbar, on the dropdown on the far left (or top), where it says "Pivot Table", choose Formulas > Calculated field. In the resulting window, put a name for the field in the first box, in the one below that will have the formula, use the table fields below to select the appropriate fields, putting in the divisor sign (/) between them:

=SumOfLength/CountOfeqinit

Then click OK. Now it should have that division in the Pivot Table.
________
Honda Motocompo Specifications

 

[shades]

Here is the completed Pivot.

Is this what you wanted?
________
Bmw M5 (E34) History

 

[kidrobot]

shit how did you do that

 

[kidrobot]

oh you did it in formulas huh?

 

[shades]

Yes, look at post #6 that describes how I did it.
________
Toyota fortuner

 

[kidrobot]

Yes, look at post #6 that describes how I did it.

ooops yeah I over looked that somehow. Thanks a ton shades!

 

[kidrobot]

One more little problem. I tried playing around with it for 30 min and I can't figure out why it isn't giving me the Average of "Sum of Field1" of each train. Any clue why?

 

[shades]

This is where Pivot Tables can be frustrating. In fact, two years ago after reading and using chapter 12 of VBA and Macros for MS Excel by Bill Jelen and Tracy Syrstad, I have often used VBA for creating Pivot Tables and setting up formulas based on a copy of the Pivot Table, and final formatting (all in one macro). This gives you all the benefits of Pivots, plus the entire range of XL capabilities for just this kind of thing. And once you get the hang of it, VBA for Pivots is actually easier/faster than Pivots themselves. Bill and Tracy provide all the steps to get it done. Some of the code allows me to do this in less than 20 seconds - which is better than Pivot creation in XL, just by itself.
________
Buy iolite

 

[kidrobot]

So I guess you're suggesting using VBA. I know how to do certain VBA things in Access but not so much in XL. Do you by chance have an example of what I need?

 

[shades]

I have one. It may take a little while to find, because of work projects.
________
Buy air one vaporizer

 

[kidrobot]

I have one. It may take a little while to find, because of work projects.

Thanks that would be great!!

 

[shades]

I have modified example for several projects. It sets up a Pivot Cache, builds Pivot Table, copies Pivot data (PasteSpecial), then deletes the Pivot Table. Thus, you re-create the Pivot every time you run the code. The worksheet is called Data, and the Pivot Table is built on the same worksheet, offset two columns to the right of the data. Also, this code automatically adjusts for the size of the data (columns is set for 8 columns, but can also be setup like the rows to resize for new columns).

Sub CreateSummaryReportUsingPivot()
    ' Create Pivot Table (PT) for a summary report
    ' Region in the rows in PT and products as columns in PT

    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Set WSD = Worksheets("Data")
    
    ' Delete any prior Pivot Tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
    
    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(65536, 1).End(xlUp).Row
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
        
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"), TableName:="PivotTable1")
    PT.ManualUpdate = True
    ' Set up row fields
    PT.AddFields RowFields:="Region", ColumnFields:="Product"
    
    ' Set up data fields
    With PT.PivotFields("Revenue")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    
    ' Code does not display total row or column
    With PT
        .ColumnGrand = False
        .RowGrand = False
        .NullString = "0"
    End With
    
    ' Calculate PT
    PT.ManualUpdate = False
    PT.ManualUpdate = True
        
    ' PT.TableRange2 contains the results. Move these to J10 (offset 2 col)
    ' as values, not a real PT.
    PT.TableRange2.Offset(1, 0).Copy
    WSD.Range("J10").PasteSpecial xlPasteValues
                
    ' Delete original PT & the Pivot Cache
    PT.TableRange2.Clear
    Set PTCache = Nothing

End Sub

I highly recommend getting the book VBA and Macros for MS Excel, Bill Jelen and Tracy Syrstad. They explain the code and offer other variations; this code is modified slightly from the book.
________
Vaporizer wholesale

 

[kidrobot]

thanks, I'll check out that book.