PDA

View Full Version : [SOLVED] Find average of all values between two index match values in table



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.

RINCONPAUL
05-11-2016, 04:07 PM
Thinking more about this, instead of AVERAGE, it would be better (and maybe easier) to SUMPRODUCT between & including the two lookup values. Another workaround might be to return a cell reference for each lookup and then SUMPRODUCT between the two references?

RINCONPAUL
05-11-2016, 05:14 PM
Solved it, but a Guru might be able to improve on my multi stepped solution?