Avg between several fields in advanced formulas

  • 1
  • Idea
  • Updated 4 months ago
  • Implemented
We would like to see the ability when using advanced formulas to be able to divide by the number of column that have data v having to choose a set number.

IE
Column A 4
Column B Null
Column C 2

We would like the average to read 3 instead of 2.
Photo of Chris Mudd

Chris Mudd, Employee

  • 3,752 Points 3k badge 2x thumb

Posted 4 months ago

  • 1
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,950 Points 20k badge 2x thumb
Hey Chris, I'm not following your example. Did you mean rows of data instead of columns?
Photo of Jitin Mehndiratta

Jitin Mehndiratta, Product Manager

  • 3,264 Points 3k badge 2x thumb
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
Photo of Pele

Pele

  • 2,664 Points 2k badge 2x thumb
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 :)
 
Photo of Kunal Bhat

Kunal Bhat, Employee

  • 1,002 Points 1k badge 2x thumb
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