liveinhope
Well-known Member
- Joined
- Dec 16, 2013
- Messages
- 857
With "normal" formulas when you copy the formula down to rows below the relative cell adresses change to refer to the data on the other rows.
But With GETPIVOTDATA copy down copies like absolute cell addresses (my pivot table and GETPIVOTDATA formula below)
In the end I entered (by pointing ) the formula seperately each of the row values "North","South" "East" "West"
Not too bad in this instance as there were only 4 different row values but if there were a large number that would be a real pain !
Is there a way round this ?
formula
=GETPIVOTDATA("AVG SHIPD",$F$1,"br","east")/GETPIVOTDATA("AVG ORDER",$F$1,"br","east")
<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
<COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 2048" width=112><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 1865" width=102><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 1609" width=88><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 1865" width=102><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2340" width=128><TBODY>
</TBODY>
But With GETPIVOTDATA copy down copies like absolute cell addresses (my pivot table and GETPIVOTDATA formula below)
In the end I entered (by pointing ) the formula seperately each of the row values "North","South" "East" "West"
Not too bad in this instance as there were only 4 different row values but if there were a large number that would be a real pain !
Is there a way round this ?
formula
=GETPIVOTDATA("AVG SHIPD",$F$1,"br","east")/GETPIVOTDATA("AVG ORDER",$F$1,"br","east")
<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
Values | ||||
Row Labels | Sum of ordered | Sum of shipped | AVG ORDER | AVG SHIPD |
east | 125 | 40 | 41.6666666666667 | 13.3333333333333 |
north | 170 | 55 | 56.6666666666667 | 18.3333333333333 |
south | 70 | 55 | 35 | 27.5 |
west | 50 | 35 | 25 | 17.5 |
Grand Total | 415 | 185 | 41.5 | 18.5 |
<COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 2048" width=112><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 1865" width=102><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 1609" width=88><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 1865" width=102><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2340" width=128><TBODY>
</TBODY>