function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
CarrieLeeCarrieLee 

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!
TCAdminTCAdmin
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)
)
CarrieLeeCarrieLee
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.
TCAdminTCAdmin
CarrieLee,

It depends on what type your fields are as to how you would need to process them. If they are text fields you can surround the field with the VALUE() formula. This will convert numeric characters to their numeric value. If you are using picklists for 1-10 you will need to utilize the CASE() function. This can then change the text value to a numeric value which can then be used in a mathmatic function.
CarrieLeeCarrieLee

It's a survey in which the respondant would rate something on a scale of 1-10 and as I mentioned sometimes a question (Question 2.) does not apply. The interviewer types the response in a number field. We average all of the responses to each question using Step Two of the report builder (Select the information to summarize), however as I mentioned if we exclude in Advanced Filters "Rating 2 not equal to blank" it excludes the entire survey. If we include question 2 and it was not applicable it skews our average. I tried creating a validation rule, but you can't use a number field when selecting a dependent field and if we use a text field then the questions are not included in Step Two as information you can average.

 
TCAdminTCAdmin
CarrieLee,

The reports will not do what you are looking for. You will need to create the calculate on the records themselves with the formula field above and then report on that field. If you calculate the average for that record you can then use the report to calculate the average of all of the records.