Analyzing survey data - which has many occurrences where a person did not answer a question. I am using vlookup to convert text answers (very important, important, etc) to numbers, and inserting null values if the person did not answer. =IF(I2="","",VLOOKUP(I2,VLookup!$A$2:$B$34,2,FALSE)).
To test, I temporarily added a column and used =ISBLANK(I551) formula to ensure null values.
In the data, when I highlight one of the new columns, excel properly displays the count (including null values), the average (does not include records with null value).
However, the pivot table is returning (#N/A) for this same data when I am looking for AVG (or SUM). Pivot table counting all records (including null values) in the count - which I do not need, but fyi).
Any advice appreciated.
