STDEVIF Function?

[rjbinney]

I know I can do this in Pivot Tables. I want to do this with formulas if possible.

I have a list of products (about 30) (named "SKU") and how long it took to run each (named "Actual") (some ran two or three times, some ran up to 60 times). I need some information about how "in control" the process is. I have a table of the standard runtime for each, and inserted a MATCH column ("Should").

I calculated a column for the delta between "Actual" and "Should" for each run (named "Delta").

So to get the average difference between standard and actual, I can build a table:

PRODUCT	MEAN RUN DIFFERENCE
Product 1	=SUMIF(Delta, SKU, $A2) / COUNTIF(SKU,$A2)
Product n

<tbody>
</tbody>

I would love to look at the volatility - which means I need to be able to compare the Delta to the Mean. Is there a clever formula that will tell Excel to ONLY consider based on, say, SKU criteria.

It's that last bit - basing it on criteria that's important. Because in addition to SKUs, I also have columns (and need to cut data) based on corresponding equipment, region produced, end product, customer, etc. So it's super-easy to build tables to calculate the mean (based on above formula). It's the standard deviation I'm stuck on!

Again, I know I can pull it together with Pivot Tables, but I have my reasons for not using 'em.

Thanks,

Thanks,

 

[rjbinney]

I used the first option, and it worked. Oddly, it worked without making it an array (Ctrl+Shift+Enter) - just as a normal formula.

Thanks for explaining it. I kinda have a handle on SUMPRODUCT, but this really helps.