I am trying to create an "Average" field to calculate the average of multiple columns in a pivot table (each column separately that is) knowing that the rows under each column will change according to slicer selection. I was able to create the formula to calculate that but i just noticed that once the reference word in my formula (which is "grand total") falls after row 99 then the formula gives no results !!
here is the formula
=IFERROR(AVERAGEIF(INDIRECT(B10&"15:"&B10&RIGHT(CELL("address",INDEX($A$13:$W$101,MATCH("Grand Total",$A$13:$A$101,0),1)),2)-1),"<>0"),"")
any suggestion?
Solved by A. H. in 24 mins