Alternate State Set Analysis Help

[erisman20]

Hey everyone,

I am dealing with an issue I cant quite figure out. I have 2 alternate states (State1, State2) and I want to to find the rank with the values of State1, including the aggregated average from State2. See example below:

State 1:

Id	Value
1	10
2	11
3	12
4	14
5	15

State 2:

Id	Value
6	12
7	13
8	14

Result:

Id	Value	Rank
1	10	6
2	11	5
3	12	4
State 2	13	3
4	14	2
5	15	1

[erisman20]

How would you add the Standard deviation of the new set, including the "State2" value?

[sunny_talwar]

Check the attached

[erisman20]

Sunny,

Sorry to keep this post going:

Is there any way to do this same functionality without using straight tables?

For example, if I just wanted to create a text box with the rank of the aggregated State2 values compared to the State1.

Thanks for the help!

[sunny_talwar]

What would be the output look like in the text box? Can you draw a picture for me?

[erisman20]

Using the same data values as above, I would want to be able to to do the following without using tables:

[sunny_talwar]

Getting Average value should be simple

='Average State2 Value' & Chr(10) & Avg({State2} Value)

Still trying to figure out the Rank part

[sunny_talwar]

For rank, try this

='State2 Rank' & Chr(10) & RangeSum(Count(DISTINCT Aggr(If(Sum(Value) > Avg(TOTAL {State2} Value), Id), Id)), 1)