I’m having an issue with Excel Pivot Table recognizing a column as text instead of numbers.
I have a table (1) with a column (A) that calculates the variance between 2 other columns (B & C). However, the formula returns a blank cell instead of a 0 if there is no value B or C. The reason for this, is because I have an AGGREGATE function averaging column A (ignoring hidden, blanks and errors), so the average changes depending on other filtering applied to the table. I also don’t want to include these blanks as zeros in the average.
The problem occurs because I also have a pivot table referencing Table 1, also averaging column A. However, when there are blank cells present in column A, the pivot table seems to recognize this as text instead, and simply removes the field altogether after a refresh. The same issue occurs if I have the formula default to an error instead of blank.
I believe my issue is similar to this: Excel 2010: if( , , "") not treated the same as blank for pivot table group by date
I have no idea how to resolve my problem with the pivot table, while maintaining my aggregate function in my original table. Hoping the community here can help me out.
Thanks in advance!