- General Development
- Schema Development
- Apex Code Development
- Visualforce Development
- Formulas & Validation Rules
- Force.com Sites & Site.com
- Chatter Development
- Java Development
- .NET Development
- Perl, PHP, Python & Ruby
- Desktop Integration
- APIs and Integrations
- Visual Workflow
- Apple, Mac and OS X
- VB and Office Development
- AppExchange Directory & Packaging
- Salesforce Labs & Open Source Projects
- Other Salesforce Applications
- Jobs Board
11-01-2012 10:47 AM
I've got a custom object with number fields on it. I want to be able to report on evaluations entered into that object and those fields using an average formula. I've made it that far, but I need it to not make blank values count against the average. For instance, if I want an average of one field from that object, 1stField__C may have a total value of 25. The average of that from 5 evaluations would be 5. But in that same report, I want to average 2ndField__C. It may have one blank value, and total to 20. If it is diving the record count into that (5 records), it's 4, but one is blank. It should be diving the number of values that do not equal blank into the total (20 divided by 4, not 5) and then it would give an average of 5. Not 4.
I know that is a run on like crazy, but any help would be appreciated. Thanks!
11-01-2012 08:16 PM
I also faced this problem .
But i have done a work around for it .It may help you.
The work around is i have created one more field(New__c) and update that value with workflow.
when the field (Actual field__c) value not equal to 0.
And in the report i have done the average based on the (new__c).
This helped me to get exact record count.
11-02-2012 11:56 AM
I think this is what you're looking for:
( Num1__c + Num2__c + Num3__c + Num4__c + Num5__c ) / IF( ( IF( Num1__c = 0, 0, 1) + IF( Num2__c = 0, 0, 1) + IF( Num3__c = 0, 0, 1) + IF( Num4__c = 0, 0, 1) + IF( Num5__c = 0, 0, 1) ) = 0, 1, ( IF( Num1__c = 0, 0, 1) + IF( Num2__c = 0, 0, 1) + IF( Num3__c = 0, 0, 1) + IF( Num4__c = 0, 0, 1) + IF( Num5__c = 0, 0, 1) ) )
The dividend is the sum of all the fields.
The divisor checks whether the value in each field is 0, and if not, increments by 1. So if only 3 fields have non-zero values, it divides by 3. However, it also checks whether ALL the fields are empty, and in that case sets the divisor to 1, so you don't get a divide-by-zero error.
If you're treating empty number fields as blanks, rather than zero, you might need to change each IF statement from
IF ( Num__c = 0, 0, 1)
IF (ISBLANK(Num__c), 0, 1)
Senior Product Manager, Declarative Apps