I have a pivot table whose data can be filtered by selecting multiple months. It's last column is a grand total figure, and the column to the right of that is supposed to be a monthly average (grand total / # of months). Right now the formula for average is =IF(E34="","",E34/3), i.e. it is hardcoded for the average of 3 months. However if more months are selected in the pivot table month filter, this will be incorrect. Is there a way to find the number of selected items in a pivot table filter?
Thanks.
Yes, a few ways actually. To officially answer your question, you could do a counta formula, which looks for cells that are not empty. Not a good idea here because you'll have a header and grand total, but it can be done =COUNTA(F4:F17)-2 would take the number of cells not empty, minus 2 for the header and total row to return 12 if it were referencing a pivot table with all 12 months filtered in.
My advice would be to do this:
- add the field you want an average of to the Values section of the
field list (you can select the same item more than once in pivot tables)
- left click on it (it'll be in the bottom right) and
choose "Value Field Settings"
- under Summarize value field by, select Average (by default, either count or sum will most likely be selected)
- Now you have a new column with a monthly average
- if you do not need the sum, you can simply edit the existing column
Good luck!
