Create an ARRAY within LARGE using GETPIVOTDATA

[RedOctobir]

I've got a report that is showing trended data, but there are some sporadic data integrity issues. We're trying to correct the problem by replacing the bad value with the average of the two largest items available.

The data is dynamic depending on what the user selects so we're using the GETPIVOTDATA formula.

Basically this is what I need:

AVERAGE(LARGE({GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,-1)),GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,-2)),GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,-3)),1),
LARGE({GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,-1)),GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,-2)),GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,-3)),2))

But I can't seem to figure out how to create an array from multiple GETPIVOTDATA formulas. Any ideas? Is this even possible?

Thanks!

 

[mart37]

Maybe this can help: =LARGE(GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,{-1;-2;-3})),1)

 

[mart37]

or this: =AGGREGATE(14,6,(GETPIVOTDATA(FieldName,Location,Month,EDATE(A$2,-ROW(1:10)))),1)

 

[RedOctobir]

Mart you are amazing. That works perfectly. Thank you!