Avg between several fields in advanced formulas

Hey Chris, I'm not following your example. Did you mean rows of data instead of columns?

---

Hi Chris,





Average across columns can be accomplished using Numeric Expression. Example: If there are 3 columns, Column A, Column B and Column C. 


You can set up a numeric expression like (Column A + Column C)/2 . 





In your case, Do you want to calculate average across Row Data or across columns. There is no predefined formula for calculating average across columns. However, it can be accomplished using numeric expression.





Regards,


Jitin

---

By the example, it sounds like you don't want to include Null values in the denominator, thus needing a variable denominator instead of a set value. I've setup a bionic rule to do that for averages– but you have to created an additional formula field that counts (and potentially adds) the values you want to include in the denominator. 





In my example (a bit different, but should work for this use case), we had 3 different relationship scorecards for the same relationship type, and accounts can have multiples of that relationship type with any combination of the 3 scorecards. I wanted to set the Average of all Overall relationship scores as the score for the measure on an Account scorecard, but kept running into denominator issues as the value was variable and I couldn't use a set number.





So, I summed the Overall values for each scorecard type by relationship, but also counted the number of relationships where each scorecard was currently present. The formula then looked something like this:





(SUM Sc1 + SUM Sc2 + SUM Sc3) / (Count Sc1 + Count Sc2 + Count Sc3)





Hope this helps :)


 

---

Hi Chris,





Are you using the advanced formula builder in data management? If that's the case, here's another workaround using CASE statements if you do not want to include null values in your denominator:





CASE WHEN ColumnA IS NULL THEN (ColumnB + ColumnC)/2 WHEN ColumnB IS NULL THEN (ColumnA + ColumnC)/2 WHEN ColumnC IS NULL THEN (ColumnA + ColumnB)/2 ELSE (ColumnA + ColumnB + ColumnC )/3 END 





Hope this helps!





Thanks,


Kunal

---