Excel Formula Efficiency 11: Pivot Table Faster Than Formula

I am trying to enter a simple formula in a column of a pivot table. My problem is that when I enter the formula [=C2/C7] the cells below in that column automatically get changed to [=C2/C8], [=C2/C9], [=C2/C10], etc... I do not want this. C7 is the denominator I need to use for all...

How do I stop this from happening automatically?

Here is how my data looks in the pivot table.. The dashes represent the next column


Location - Employee - Result

Vermont - John Smith - 5
- Frank Parker - 3
- Sally Field - 0
- Tim Hutton - 0


New Jersey - Fred Taylor - 9
- Mary Sky - 5
- Heather K - 4
- Tom Smith - 0
- Mark Crawford - 0


What I am trying to do is put a formula that will calculate the % of reps that have 0 for their locations. So for Vermont it would be 50% and for New Jersey it would be 40%. The difficultly is their are a varying number of reps for each location and I have over 100 locations on the pivot table. I am not sure if a calculated field can get this accomplished for me? Any help would be greatly appreciated.



Thanks,
Jared

I need to create a customised calculation which basically caluclates the % difference between two prices.

I managed to create the formula, however at sub-total and grand-total level i am getting an incorrect answer possibly to 0 values in some of the individual line items.

Is it possible to create the same formula outside the pivot table in a column next the actual pivot table and then easily copy down into other cells?


regards,

Hi all!

I am using Excel 2007 and I have data that i'm trying to look at thru a pivot table. I can create the pivot table but then I need to create a new field that will create an average instead of a sum.

The reason i need an average is becase the data is actually Scores given by customers to rate our technicians and I just need the average.

I thought all i would need to do is select PivotTable Tools-> Options -> Tools -> Formulas and click on Calculated Fields and imput the formula i want (example: Average). But that seems not to work either.

Maybe a pivot table isn't the right thing to use, any suggestions would be GREATLY appriciated.

I have attached an example file of what I am trying to do.

My company has a report we generate by our back-end that gives raw data for all our open (un-shipped) orders. I have a macro that uses this data to generate a pivot table from the data.

However, sometimes we want to see only the orders that contain a particular SKU (product). One way to achieve this is to manually hide the pivot items for the other SKUs (uncheck show all, check the ones you want to see). The problem with this method is that we can then only see the pivot items for the tracked SKUs, and not everything else that is on the order.

In other words, I need a method to show me all orders that contain the search term, plus everything else on the order regardless of whether it matches the search term.

To that end, I wrote the following function. It works, but my problem is that it is dog slow because of the For loops that individuall hide and unhide the SKU pivot items. Is there some faster method for achieving my desired outcome?

VB:

Sub OpenOrdersFindSKU() 
     'This function asks the user to input a SKU, and then formats the active pivot table
     'to display only purchase orders that contain that SKU.
     
    Dim i As Integer 
    Dim SKU As Integer 
    Dim Max As Integer 
    Dim Order() As String 
    Dim Count As Integer 
    Dim foo As String 
     
     'Find the Endrow
    Dim endRow As Long 
    endRow = Cells(Rows.Count, 2).End(xlUp).Row 
     
     'Disable screen updating
    Application.ScreenUpdating = False 
     
    Max = ActiveSheet.PivotTables("OpenOrders").PivotFields("Order#").PivotItems.Count 
    Redim Order(1 To Max) As String 
     
    SKU = InputBox("Enter SKU to find:") 
    Count = "0" 
     
     'ActiveSheet.PivotTables("OpenOrders").ManualUpdate = True
     
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     'First, display only the lines for the SKU we want
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For Each PivotItem In ActiveSheet.PivotTables("OpenOrders").PivotFields("SKU").PivotItems 
        If PivotItem.Value <> SKU Then PivotItem.Visible = False 
    Next PivotItem 
     
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     'Next, collect the Order numbers for those lines, and put them in our array Order()
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For i = 1 To endRow 
        Range("E" & i).Select 
         
        If ActiveCell.Value = SKU Then 
            Count = Count + 1 
            Order(Count) = ActiveCell.Offset(0, -2).Value 
             'Debug.Print POs(Count)
        End If 
    Next i 
     
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     'Next, go through all PivotItems in the PivotField "Order#" and deselect those that are not
     'in the Order() array
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For Each PivotItem In ActiveSheet.PivotTables("OpenOrders").PivotFields("Order#").PivotItems 
         'Debug.Print "PivotItem.Value " & PivotItem.Value
         
        On Error Resume Next 
        foo = WorksheetFunction.Match(PivotItem.Value, Order, 0) 
         'Debug.Print Err.Number
         
        If Err.Number <> 0 Then PivotItem.Visible = False 
        On Error Goto 0 
         
    Next PivotItem 
     
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     'Finally, display all SKUs again so we see everything on the orders we picked
     '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    For Each PivotItem In ActiveSheet.PivotTables("OpenOrders").PivotFields("SKU").PivotItems 
        PivotItem.Visible = True 
    Next PivotItem 
     
    ActiveSheet.PivotTables("OpenOrders").ManualUpdate = False 
     
    Call OpenOrdersPivotTableFormat 
     
    ActiveWindow.ScrollColumn = 1 
    ActiveWindow.ScrollRow = 1 
     
    MsgBox ("Found SKU " & SKU) 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Hello - I am working with a set of survey data (503 responses to 237 questions) and creating cross-tabs in excel. Rather than using "sum" to look at the totals, I actually want the average of the data and in some cases, the count. It is incredibly time consuming to change each row inidividually, is there a way to change a group at once - or potentially change the default?

Thank you!!

I've searched the board and found a way to extract the unique values using the advanced filter but the problem is it doesn't filter everytime I change the pivot values - Is there a forumula that would basically extract the unique values without spaces from the table?

Cusip Mark? Hedge Name 3128K06L2 Yes FGLMC 4.5 3128K8BA3 Yes FGLMC 4.5 3128K8GU4 Yes FGLMC 4.5 3128LXC32 Yes FGLMC 4.5 3128LXFA3 Yes FGLMC 4.5 3128M4HW6 Yes FGLMC 4.5 3128M4JQ7 Yes FGLMC 4.5 3128M5AR1 Yes FGLMC 4.5 3128M5SY7 Yes FGLMC 4.5 3128MJCV0 Yes FGLMC 4.5 3128PMNQ9 Yes FGCI 4.5 3128PPMJ9 Yes FGCI 4.5 31297TX57 Yes FGLMC 4.5 31297VLJ5 Yes FGLMC 4.5 31398EP81 Yes FNCL 5 31398EPF5 Yes FNCL 5 31398EPG3 Yes FNCL 5 31398EPH1 Yes FNCL 5

That's the pivot table,

I'd just want another column that would return the following:

FGLMC 4.5
FNCL 5
FGCI 4.5

any ideas?

I have a formula that works but only 3 times before it becomes ineffiecient

=IF(ISNUMBER(SEARCH(M1,H66))=OR(ISNUMBER(SEARCH(M2,H66)))=OR(ISNUMBER(SEARCH(M3,H66))),"OK","Not OK")

The M range is "M1 to M24" but this doesn't work:

=IF(ISNUMBER(SEARCH(M1:M24,H66))),"OK","Not OK")

Can someone help me get the correct efficient formula?

Cheers,

I need to know the Excel (in cell) formula for the following equation:

LOAD x .177
(2*3.14159) x (TORQUE)

Where the LOAD = A1
&
Where the TORQUE = B1

Thanks,
13blues