Skip to main content
chris_mudd
Gainsight Employee ⭐️
chris_mudd
Gainsight Employee ⭐️
June 12, 2018

Avg between several fields in advanced formulas

  • June 12, 2018
  • 4 replies
  • 78 views
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.

      4 replies

      dan_ahrens
      dan_ahrens
      Expert ⭐️⭐️⭐️
      June 13, 2018
      Hey Chris, I'm not following your example. Did you mean rows of data instead of columns?
      VP of Customer Success at Forcepoint
      jitin_mehndiratta
      jitin_mehndiratta
      Helper ⭐️⭐️⭐️
      June 19, 2018
      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
        pele
        pele
        Helper ⭐️⭐️⭐️
        June 19, 2018
        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 :)
         
          kunal_bhat
          kunal_bhat
          Helper ⭐️⭐️⭐️
          June 20, 2018
          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
          Terms & ConditionsAccessibility statement