I've tried to add a (calculated field) in a pivot table to calculate an average. When I write the formula which is (=SUM("Box Office Revenue ($)")/count("Box Office Revenue ($)")), I get a Value Error with a massage telling me that the parameter of sum function isn't numeric.
I made sure that I formatted this parameter as numeric in the original dataset and also in the pivot table.
CodePudding user response：
This doesn't look right to me: (=SUM("Box Office Revenue ($)")/count("Box Office Revenue ($)")) The parenthesis don't make sense. And you seem to be putting named ranges in quotes (which makes them strings instead of named ranges).
Adding further confusion, you seem to have included parentheses in the actual name of a named range: Box Office Revenue ($).
If that is, in fact, the name of a named range, try this:
=SUM(Box Office Revenue ($))/COUNT(Box Office Revenue ($))
Maybe you aren't referencing named ranges but headers used as database tags. In that case, try this version with quotes:
=SUM("Box Office Revenue ($)")/COUNT("Box Office Revenue ($)")
... or with single quotes:
=SUM('Box Office Revenue ($)')/COUNT('Box Office Revenue ($)')
If neither of those work, consider sharing a link to your spreadsheet or a copy of it. That may be the only way for those here to figure out what you're trying to do and why it isn't working for you.
Note: Single quotes around header from original table is what turned out to work inside a pivot table's custom calculation formula field.