3

Let's assume I have two columns in excel, dates as rows and I want to create a pivot table with some calculated field. Formula for calculated field is Column1 / AVERAGE(Column2), depending on dates selected.

I use vba. but when I do 

pvtTable.CalculatedFields.Add Name:="Test1", Formula:="Column1 / AVERAGE(Column2)"

AVERAGE is being count from sum of values in column 2 (for selected date range), not single values. EDIT: AVERAGE seems to be ignored in calculated field's formula and sum is taken instead.

Is there any way to use single values when using AVERAGE in calculated field's formula? I use Office 2010.

More details

I tried to do this:

With pvtTable
    With .PivotFields("Column2")
        .Orientation = xlDataField
        .Function = xlAverage
        .NumberFormat = "0.00"
        .Value = "Avg Column2"
    End With
End With
pvtTable.CalculatedFields.Add Name:="Test2", Formula:="Column1 / Avg Column2"

But Test2 isn't shown on the pivot table's field list. When I want to display it from vba, I get an error Unable to get the PivotFields property of the PivotTable class.

Community
  • 1
  • 1
maialithar
  • 3,065
  • 5
  • 27
  • 44

1 Answers1

0

You are almost there. In your first try you need to change the formula:= to be an actual formula. The = that follows the : doesn't start your formula (see the name argument). It's a confusing bit of syntax. then you need single quotes around the column names.

This should do the trick:

ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add _
    "Test1", _
    "='Column 1'/AVERAGE('Column 2' )", _
    True

Update: this does not work. You cannot use formulas in the calculated field that require range references.

http://www.ozgrid.com/Excel/pivot-calculated-fields.htm

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
  • Thanks for the answer, but it doesn't help. `='Column 1'/AVERAGE('Column 2' )` evaluates to the same value as `='Column 1'/'Column 2'`. The reason is explained [here](http://www.excelgirl.com/2011/09/how-to-insert-a-calculated-field-in-a-pivot-table/) in rule #2. I believe what I want to achieve is not possible with this technology. – maialithar Oct 31 '13 at 07:04
  • Looks like you are right. You cannot use excel formulas that require ranges in the calculated field option of pivot table. It's possible that powerpivot may do the trick, but that is a separate install, and not something I am familiar with. – guitarthrower Oct 31 '13 at 16:12