Calculating Standard Deviation in spreadsheet with several SUMPRODUCT cells.

[rfinnegan]

Hi All:
This is a modified re-post of a question I asked last week. I received a couple of replies (thanks again) that got me further down the road....to the point of realizing I needed more than what I asked for. So here we go again:

I have a large worksheet of transaction data from about 50 stores and over 500 cashiers. Within this worksheet I'm trying to calculate some averages and standard deviations. The issue I have is that I need to calculate each stores average separately and then compare the cashier from that store to his/her store's average AND how many Standard Deviations the cashier is from the store average.

For example, store 'Main' has 29 cashiers. In column A is the store name, Column B is the cashier ID, Column C is the transaction data which in this case is ATV (Average Transaction Value).
So for rows 2-30 (row 1 is a header) it lists the above info and I use the SUBTOTAL function to to get the store average for the Column C data (transaction), and also the Standard Deviation.

Now I need a way to calculate how far each cashier is from his/her store's average (which is in C31). The formula I'm using is simple, but I have to modify it for each store. Here's what it looks like for the first cashier of the main store:
=ABS(c2-$c31)/d$31.
D31 is the Standard Deviation of C2:C30.

Is there a formula to get past the process of manually adjusting the cell that represents the store average and standard deviation?

Thanks in advance.

 

[Little_Clubber]

Delete your STDEV in D31, the complete formulae you'll need is as below:
Put this into Cell D2 and then fill down as far as you want to go. It calculates the STDEV and Average within the formulae itself so you don't need to reference any other cells.

=IF(RIGHT(A2,5)="Total","",ABS(C2-SUMIF(A:A,A2,C:C)/COUNTIF(A:A,A2))/STDEV.P(INDIRECT("C"&MATCH(A2,A:A,0)&":"&"C"&MATCH(A2,A:A,1))))

 

[rfinnegan]

Brilliant!
Worked perfectly.

Thanks a million.