Range compariosn - best of

[jgannon]

I have multiple sets of ranges (10) and I would like to show the best
of. Currently I have nested if statements to give me the "best of",
but I was wondering if there was another/better way of doing this? Any
Suggestions?

This is what the ranges look like
Group A
rate 15 30 45 60
5 100 101 102 103
5.125 101 102 103 104
5.250 103 104 105 106
AVG(x) x x x x

Group B
rate 15 30 45 60
5 101 102 103 104
5.125 102 103 104 105
5.250 104 105 106 107
AVG(x) x x x x

x = average of that column (15,30,45,60)
AVG(x) = the average of the values of x
Max(Avg(x)) = gives the highest average between all ranges

I am currently using a max function on the [AVG(x)] to determine which
group is showing the best pricing.

Once I have that value, I am using another IF statement to say
IF(a10=Max(avg(x)), Group A, IF(e10=Max(avg(x)), Group B... and so on.


Is there a simpler way to do this besides a nested if statement?

 

[Bernie Deitrick]

j,

Yes, there is a better way, usinge a combination of OFFSET and MATCH.

Select a block of cells that is the same size as the table that you are interested in, say 5 by 5,
and use an array-entered formula (enter using Ctrl-Shift-Enter) like:

=OFFSET($A$1,MATCH(MAX(A:A),A:A,FALSE)-6,0,5,5)

Replace the 6 with the number of rows of each block + 1 (5 + 1 = 6). For this example, the AVG(x)
formulas are in column A, and there shouldn't be any other numbers in column A that could cause the
MAX to return a value other than one of the AVG(x) values.

HTH,
Bernie
MS Excel MVP