Hello everyone.
Is there a pivot table option in Excel for non-numeric data in pivot tables to be excluded from the “Grand Total” calculation?
Example:
Project ................Avg of Percentage Saved (Lost)
Job 1 ........................................25%
Job 2 ........................................40%
Job 3 ........................................15%
Job 4 ........................................N/A
Job 5 ........................................13%
Grand Total: ...............'Error Syntax Displayed'
So, in the above scenario, can the average for jobs 1,2,3, and 5 be calculated? I of course get an error value in these situations and was just curious as to whether there is an option that can be selected in the Pivot Table Options to exclude any non-numeric items from the calculation. NOTE: Error values in my source data set are conditionally formatted to read "N/A" in my pivot table (i.e. "Job 4").
I can just uncheck the “Job 4” in the column filter, but that could get tedious with a large number of jobs, plus there may be numeric data in an adjacent column/cell that would be excluded if I removed the whole row.
Thanks!!!
Bookmarks