J
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?
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?