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

# Set Analysis - Conditional Average

Hi,

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

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,

Jason

• ###### Set Analysis - Conditional Average

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

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

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.

• ###### Set Analysis - Conditional Average

Hi,

May be try with this one

Count({<Account=,Group={'\$(=MaxString(Group))'},Ratio={"<\$(=Max(Ratio))"}>} Account)

Celambarasan

• ###### Set Analysis - Conditional Average

Thanks for everyone's help.  Jason your second suggestion did work...I was just reconciling it to the wrong number so I thought it was incorrect.

So the final formula looks like this:

=Count( {1< Group=P(), Ratio = {"<\$(Only({<Ratio=P()>} Ratio))"}>} Account)

This formula works also:

=Count( {1< Group=P(), Ratio = {"<\$(Max(Ratio))"}>} Account)

• ###### Set Analysis - Conditional Average

Good stuff.  Please mark the correct answer so others can see it!