Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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...
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
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.
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...
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.
Hi,
May be try with this one
Count({<Account=,Group={'$(=MaxString(Group))'},Ratio={"<$(=Max(Ratio))"}>} Account)
Celambarasan
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)
Good stuff. Please mark the correct answer so others can see it!