RINCONPAUL
05-11-2016, 01:33 PM
Using index match to look up the intersection of two parameters is fine, but what if you want to average all values that occur (including the matches) of the result of two different index/match functions? In the attachment, I have a two way table. In Example 1 the result of two different parameter pairs is shown at O2 & P2. As they will always share the same RANK parameter (in same column) cell Q2 finds the average of those two values : Average of $1,201 and $1,118 = $1,159.
However in Example 2, the variable parameters differ from 5% to 10% with same RANK of 5. I'm looking for a function to place in col Q that will find the average of all values (including and between) the Return1 and Return2 values. In this case it'll be the average of (-$8,677, -$19,927, $9,487, $-14,077, -$24,881, -$919) = -$9,832.
However in Example 2, the variable parameters differ from 5% to 10% with same RANK of 5. I'm looking for a function to place in col Q that will find the average of all values (including and between) the Return1 and Return2 values. In this case it'll be the average of (-$8,677, -$19,927, $9,487, $-14,077, -$24,881, -$919) = -$9,832.