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
mskudlarekmskudlarek 

Formulas that don't average in 0 or blank values

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@@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
Shannon HaleShannon Hale

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)

 

 
Mikhail NitkoMikhail Nitko

Ok, if you want to show all records on a report, but you want the AVG to only be computed on those whose value is not blank and not on RecordCount, then you have to do this:

1) Create a formula called something like "Field Not Blank Verifier" on the records you're summerising, that verifies in a binary 0 or 1 way as such:

If (ISBLANK(summary_field)=FALSE,1,0)


2) Create the report, and on the report, you do not use the AVG function, but instead create a formula that divides by 2 SUMs.. as such:

summary_field:SUM/field_not_blank_verifier:sum

And that should give you the AVG of only those records whose summary field is not blank, but still display ALL records in the report.