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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PRODUCT
[/TD]
[TD]MEAN RUN DIFFERENCE
[/TD]
[/TR]
[TR]
[TD]Product 1
[/TD]
[TD]=SUMIF(Delta, SKU, $A2) / COUNTIF(SKU,$A2)
[/TD]
[/TR]
[TR]
[TD]Product n
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.