AverageIf function using GetPivotData

[jamezsez]

Hi All,

I am having trouble with a formula to calculate the average of a GetPivotData range (4 different values) while excluding values of 0.

I can get the formula to work using the following Average formula,

=AVERAGE
(GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",W$1,"spm_daytype",$E$2),
GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",X$1,"spm_daytype",$E$2),
GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",Y$1,"spm_daytype",$E$2),
GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",Z$1,"spm_daytype",$E$2))

But when using the AverageIfs function, I cannot work out the structure of the formula.

I would assume it would be,

=AVERAGEIF
(GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",W$1,"spm_daytype",$E$2),
GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",X$1,"spm_daytype",$E$2),
GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",Y$1,"spm_daytype",$E$2),
GETPIVOTDATA("Average_Estimated_Pax",'Data Pivot (Avg LG Entries)'!$A$4,"stn_desc",$A5,"cqtr",Z$1,"spm_daytype",$E$2),"<>0")

but this does not work...

You help is MUCH appreciated.

 

[Jerry Sullivan]

Hi jamezsez,

The AVERAGEIF function requires a range argument whereas the AVERAGE function can accept non-range arguments like numbers. Your AVERAGEIF try doesn't work because each GETPIVOTDATA expression is returning a value instead of a range reference.

Syntax:
AVERAGEIF(range, criteria, [average_range])
AVERAGE(number1, [number2], ...)

A fairly simple workaround would be to put each one of your four GETPIVOTDATA expressions in a separate helper cell, then use a simple formula like...

=AVERAGEIF(F2:F5,"<>0")

If there's some reason you really want the entire calculation in one cell, one rather lengthy way would be to use the AVERAGE function and test each GETPIVOTDATA expression separately for 0 values. It would have the form.....

=AVERAGE(IF(< GetPivotExpression1>=0,FALSE,< GetPivotExpression1>),IF(< GetPivotExpression2>=0,FALSE,< GetPivotExpression2>),....)

 

[exoticdisease]

Hi jamezsez,

The AVERAGEIF function requires a range argument whereas the AVERAGE function can accept non-range arguments like numbers. Your AVERAGEIF try doesn't work because each GETPIVOTDATA expression is returning a value instead of a range reference.

Syntax:
AVERAGEIF(range, criteria, [average_range])
AVERAGE(number1, [number2], ...)

A fairly simple workaround would be to put each one of your four GETPIVOTDATA expressions in a separate helper cell, then use a simple formula like...

=AVERAGEIF(F2:F5,"<>0")

If there's some reason you really want the entire calculation in one cell, one rather lengthy way would be to use the AVERAGE function and test each GETPIVOTDATA expression separately for 0 values. It would have the form.....

=AVERAGE(IF(< GetPivotExpression1>=0,FALSE,< GetPivotExpression1>),IF(< GetPivotExpression2>=0,FALSE,< GetPivotExpression2>),....)

I have a similar issue except that I'm using slicers with getpivotdata and I've set up a named range for each slicer target but I want the named range to be large enough to accommodate the changing size of the pivot table as it has multiple filters within it. My getpivotdata formula looks like this:

=GETPIVOTDATA("Net ARPU P&L USD",'Chart Pivots'!$CK$6,
"Market",slicer_selection_market,
"Client Type - Category",slicer_selection_client_type_category,
"Client Type - Detail",slicer_selection_client_type_detail,
"Contract type",slicer_selection_contract_type,
"Status",slicer_selection_status,
"Source",slicer_selection_source,
"Type of Venue",slicer_selection_venue,
"Year",slicer_selection_year)

Each slicer_selection named range is something like a6:a1000, b6:b1000 etc. These throw up hundreds of errors, of course, so I wrapped the whole statement in an iferror which works fine for summing the selection. However, when I want to average it, the 0s take over and it ruins the result, just giving me 0 as there are so many 0s in the resulting arrays. Is there a way to get this to work in a single cell as an average? Is there a better way to reference a slicer selection in an excel formula when you need it to be of dynamic length? As far as I can work out, you either have to feed the AVERAGE formula a smaller array, not including the errors/0s at all or you have to make the slicer_selection named ranges dynamic somehow or you have to find a way to do this using AVERAGEIFS and make the GETPIVOTDATA evaluate to a range...

Thank you so much!

 

[RoryA]

Try returning "" rather than 0 in your IFERROR. AVERAGE should ignore those.

 

[exoticdisease]

Try returning "" rather than 0 in your IFERROR. AVERAGE should ignore those.

Oooof, dude, that is embarrassing! Thank you very much... I'm so ashamed that I didn't think of that!

 

[RoryA]

Glad we could help.