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.
IE
Column A 4
Column B Null
Column C 2
We would like the average to read 3 instead of 2.
Chris Mudd, Employee
 3,752 Points
Posted 4 months ago
Dan Ahrens, Official Rep
 25,950 Points
Hey Chris, I'm not following your example. Did you mean rows of data instead of columns?
Jitin Mehndiratta, Product Manager
 3,264 Points
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
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
 2,664 Points
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 :)
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 :)
Kunal Bhat, Employee
 1,002 Points
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
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
Related Categories

Rules Engine
 691 Conversations
 323 Followers