# Thread: Formula to change as Pivot table range changes

1. ## 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

2. 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. 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

4. 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. Hi Howard

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

zeddy

6. 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

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

HowardC (2016-04-18)

8. 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. 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.
It can be tricky sometimes trying to explain the steps.

zeddy

10. 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
•