Set Analysis - Conditional Average

[Anonymous]

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.

[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,

Jason

[Anonymous]

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.

[Jason_Michaelides · Accepted solution]

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...

[Anonymous]

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.  

[CELAMBARASAN]

Hi,

     May be try with this one

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

Celambarasan

[Anonymous]

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)

[Jason_Michaelides]

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