Evaluate Each Cell and Average Range Based on Evaluation

[matt4003]

Hi All,

I am trying what I thought was a simple problem. Evaluate each cell against 1 criteria, then Average the range based on the result of the evaluation. I tried Averageifs, average(if(xxxxxx)+if(xxxxx)), and others, just no luck. I thought about Match and / or Index, but in the end I am stumped. Did lots of research too, but didn't stumble on anything that helped.

Basically I have a list of customers (hundreds of them) and their growth rates quarter over quarter. Then I have the market growth rate quarter over quarter. I want to evaluate each quarter for each customer against the market, if it grows more than the market, take the difference....then evaluate the next quarter and so on for 13 quarters, then find the average of all the quarters difference to the market where the customer grew more than the market....then do the same for when the customer grew less than the market.

I have attached an example that shows in a few steps how I do this.....but ultimately because there are hundreds of customers, I only want one calculation step, instead of several to arrive at the final average.

I hope that I have explained it okay.

Thanks in advance for your help!!

Cheers,
MattExample.xlsx

[matt4003]

Well, I think my colleague solved it.

In my example file, cell S11 enter:
{=AVERAGE(IF(($C11:$O11)-($C$6:$O$6)>0;($C11:$O11)-($C$6:$O$6)))}

Yep, as easy as I thought it should be, but I just couldn't get my head around it. Hope it can help others.

Cheers,
Matt