7 Replies Latest reply: Apr 4, 2012 2:35 PM by Jason Michaelides RSS

    Set Analysis - Conditional Average

    Jacob Mitchell



      I've been playing around with set analysis and know there is a way to do this but have so far been unable to figure it out.  To simplify my problem, I have three fields, Account, Ratio, Stat.  When the user selects an account, I would like to average all the Ratios for the other account numbers where Stat = the value for the selected account Stat. 


      I would imagine the formula would be something like the following but haven't had much luck so far

      =avg({1<Stat={$(Stat)}>} Ratio)


      Another important thing to note is that Ratio could be 'NA' instead of a number.  If it is, I would need the formula to ignore that stat in the average.

        • Set Analysis - Conditional Average
          Jason Michaelides

          I think you need something like:


          =Avg({<Account=E(),Stat=P()>} Ratio)


          Difficult to say without testing! Also, if the 'NA's cause a problem try RangeAvg() instead of Avg.


          Hope this helps,



            • Set Analysis - Conditional Average
              Jacob Mitchell

              Thanks Jason!  That helped!  So the following formula works:


              =Avg({1<Stat = P() > } Ratio)


              Now what if I needed the count of all the accounts that have a ratio less than the ratio for the selected account? 


              I can use the following to count all the accounts with a value less than 0:

              =Count({1<Ratio = {'<0'} Account)


              But, I can't seem to replace the zero with selected account's ratio value.  Any suggestions?  This doesn't work:

              =Count({1<Ratio = {'<Ratio} Account)  or a myriad of other combinations I've tried.

                • Set Analysis - Conditional Average
                  Jason Michaelides

                  Maybe something like Count({1<Ratio={"<Only({<Ratio=P()>} Ratio)"}>} Account)


                  I think your previous post is saying there will only be one account selected and therefore only one ratio value. If you can post your app I can have a bit of a play...

                    • Set Analysis - Conditional Average
                      Jacob Mitchell

                      I can't get that formula to work and unfortunately I can't post a workbook here but I'll try to be more descriptive of my data.  I think if I change the field "Stat" to "Group" it will make more sense.


                      The data is ordered this way

                      Account     Group       Ratio

                      1                 A              0.5

                      2                 B             -0.3

                      3                 A              0.1

                      4                 A              1.5      

                      5                 C             -1.2

                      6                 C              0.25

                      7                 B               2.0


                      So, if the user selects Account # 1.  I would like to determine how many Accounts in the same Group (group A) have Ratios less Account # 1's ratio (0.5). 


                      To make it simpler though, a formula that counts all the ratios less than the selected account's ratio will be a great starting point.