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,

 

[AhoyNC]

Something like this?
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G
1	Products	Run Time	Should	Delta		Products	Std
2	Product1	2:33	2:05	0:28		Product1	0:43
3	Product2	4:15	4:20	0:05		Product2	0:21
4	Product1	3:22	2:05	1:17
5	Product1	1:55	2:05	0:10
6	Product2	4:00	4:20	0:20
7	Product2	4:45	4:20	0:25
8	Product1	2:05	2:05	0:00
9	Product2	4:43	4:20	0:23
10	Product2	4:06	4:20	0:14
11	Product1	2:30	2:05	0:25
Sheet

 

[rjbinney]

I'll give it a try, thanks. I always forget that arrays are a "thing".

 

[AhoyNC]

There is an error in the formula I gave you.In the variance formula the denominator should be "sample size -1" (N-1). When I was checking the formula I hard coded in a 4 which works in the example, but won't fit your data. You will need to change the formula to (also change ranges to match your data).

[TABLE="width: 527"]
<colgroup><col width="527"></colgroup><tbody>[TR]
   [TD="width: 527"]SQRT(SUM(IF($A$2:$A$11=$F2,($D$2:$D$11)^2))/[COLOR=#ff0000](COUNTIF($A$2:$A$11,$F2)-1)[/COLOR])
[/TD]
 [/TR]
</tbody>[/TABLE]

 

[rjbinney]

I know it's been a while, but I'm back on this. And I'm missing something.

I always have trouble working with "time" - so for now, let's take your data but convert it to "whole numbers" in elapsed seconds:
[TABLE="width: 214"]
<tbody>[TR]
[TD][/TD]
[TD]Actual[/TD]
[TD]Should[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD="align: right"]283[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]25[/TD]
[/TR]
</tbody>[/TABLE]

If I use your array formula, I get this for results:

Product1=43.12
Product2=21.07
​

{=SQRT(SUM(IF(SKU=$A14,(Delta)^2))/(COUNTIF(SKU,$A14)-1))}

But if I just sort out Product1 and run a =STDEV (or STDEVA or STDEV.P) I get completely different results!

Product1 STDEV=29.66, STDEV.P=26.53
Product2 STDEV=8.08 , STDEV.P=7.23
​

(And if I grunt the numbers by hand, I get the STDEV.P result).

#SuperConfused !

 

[Eric W]

Why not just use the STDEV.P formula as an array formula?

=STDEV.P(IF(SKU=A14,Delta))

with Control+Shift+Enter.

 

[rjbinney]

'Coz I didn't know that would work! (Guess who needs to read up on how arrays work!)

Thanks!

 

[Eric W]

You're welcome!

 

[rjbinney]

OK, this opened up a world of possibilities for me.

=AVERAGE(IF(SKU="Product2",B$12:B$1510))

Has been awesome - cutting averages based on SKU.



Now, what if I want to cut on TWO variables? Say, SKU and warehouse ("WHS")?

I tried
=AVERAGE(IF(AND(SKU="Product2",Collector=5),B$12:B$1510)) and got nothing.(Literally, "0").

 

[Eric W]

You usually can't use AND in array formulas, since it works on the entire nX2 array, not just the 1X2 row. Instead you need to use Boolean operators. * (times) is the equivalent of AND, and + (plus) is the equivalent of OR. So your AVERAGE formula should look something like:

=AVERAGE(IF((SKU="Product2")*(Collector=5),B$12:B$1510))

Alternately, you can string additional IF functions:

=AVERAGE(IF(SKU="Product2",IF(Collector=5,B$12:B$1510)))

both with Control+Shift+Enter.