Formulas that don't average in 0 or blank values

[mskudlarek]

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!

[@anilbathula@]

Hi

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.

Thanks

Anil.B

[shale]

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)

to

IF (ISBLANK(Num__c), 0, 1)