Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula to change as Pivot table range changes

    I have a formula in Col C of my Pivot table , which takes the value in Col B (same row) and divides this by the total in Col B (same row as where "Grand Total" appears in Col A)

    It works fine until Pivot Table range increases due to the ageing. New data is imported each month


    It would be appreciated if someone could kindly modify my formula taking the above into account


    I have attached some sample data
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have written a macro to compute the % of total, but need this amended so that when the range increases or decreases the formula % In Column B/ the total in Col B in the same row as grand total changes for eg B4/$B$9. If next month the total is in B10, then formula must change to B4/$B$10


    Code:
     Sub Percent_of_Pivot_Table()
    Dim LR As Long
    LR = Cells(Rows.Count, "B").End(xlUp).Row
    Range("C4:C" & LR).FormulaR1C1 = "=RC[-1]/R9C2"
    End Sub

    It would be appreciated if someone could kindly assist me

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..if you think about it, assuming each of your 'Value of Warranties' are always > 0 , then, the Grand Total value in column B will always be the largest value.
    So you could have a simple formula in say, cell [C1] to get this Max value.
    ..then your pivot row formulas can just refer to this value in $C$1 as the divisor value.

    see attached

    zeddy
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..and if your values aren't always > 0, then you could just use this formula in cell [C1]
    =GETPIVOTDATA("Value of Warranties",A3)

    (the A3 in this formula is just the first cell of your pivot table, i.e. your pivot start location is cell A3)

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..there are other solutions - do you want some more?

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    OK, I made you wait for it.
    The simplest solution is just to select the field Outstanding from the PivoTable Field List, then drag it again into the Sum Values section. Then double-click on the dragged field to display the Value Field Settings dialog.
    You can change the Custom Name to "% of Total",
    then click the [Show Values As] tab, then select the Show values as dropdown and pick the option % of Grand Total
    ..then click [OK]

    Now, whenever you Refresh the pivot, the % values will be calculated within the pivot and displayed in each row, as required.

    see attached

    zeddy
    Attached Files Attached Files

  7. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2016-04-18)

  8. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for taking the time to explain how to calculate the % of total using a Pivot Table


    I have tried this on one of my workbooks where thee is a Pivot table & it works 100%


    Howard

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..I'm glad it was 100% otherwise I would've been worried. About the % of Total that is!

    Very pleased to hear you got it working OK in your workbook.
    Your feedback is very welcome.
    It can be tricky sometimes trying to explain the steps.

    zeddy

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    The % of total is working correctly

    You explained the steps involved very clearly. You are correct in stating that it is tricky sometimes to explain the steps in a clear manner so that the other person understands


    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •