Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
CarrieLee

# Advanced Filters Not Equal to Blank

We have created a survey in Custom Objects. One of the questions does not always apply so we leave the answer blank. When we go to average the scores, these are questions where the respondant replies with a rating 1-10, the average includes that question as part of the overall average. To avoid that we thought we could just create a rule in advanced filters

Question 11 not equal to and then we leave the field blank.

It works but it excludes the entire survey. UGH!

Is there a way to exclude the one question if the answer is blank, but include the rest of the questions on the survey?

Thank you!
Hello CarrieLee,

You will need to calculate the number that you should divide by to get your average. It would be similar to the one below. The first section calculates the total and prevents null values from messing up the formula. This is not always needed. The second part counts the number of fields that have a value in it. If you have six fields but only four have values it would result in an average of those four values, not the six. Please let me know if you have questions with it.

Code:
```(
IF(ISNULL(Field1__c), 0, Field1__c) +
IF(ISNULL(Field2__c), 0, Field2__c) +
IF(ISNULL(Field3__c), 0, Field3__c)
) / (
IF(ISNULL(Field1__c), 0, 1) +
IF(ISNULL(Field2__c), 0, 1) +
IF(ISNULL(Field3__c), 0, 1)
)```

CarrieLee
It's telling me my fields do not exsit.......does it matter that they are number fields? I think they have to be number fields or check boxes for Salesforce to compute averages in that Step Two of the report builder.