Help with Standard Deviation Data

[srmorgan]

I have a database of about 180,000 rows. My columns are for example, charge#, date of charge, value of charge, department #, among others. The date range is Jan 1 to June 30th.
I have to find the standard deviation for each department, using the net daily billing as my metric. I can create a pivot with dates as columns, departments as row and sum of charges. No problem.

However I need to clean the data so that any date/department combination that nets out to zero becomes a blank when doing the standard deviation. If there are no transactions, I get a blank cell in the pivot but we don't want to include the zero cells in the average and standard deviation.
There are many date/department cells on the pivot showing zero and it is too cumbersome to delete all of the transactions that net to zero.

So the question is how do I clean the data?
I am using Excel 2007 at work.

Thanks for any suggestions

 

[oldbrewer]

show us 10 or so rows of typical data please

 

[srmorgan]

Sorry I did not get back to you sooner.
I have 6 day's worth of data here but I have 180 days in my sample with about 10,000 charges per day. Here is a short example

Date Dept Charge Amount
1-Sep 17 WW2 16.00
1-Sep 17 AS3 21.00
2-Sep 17 ED4 8.00
2-Sep 17 ES3 34.00
3-Sep 17 ER4 40.00
3-Sep 17 ER4 (40.00)
4-Sep 17
4-Sep 17
5-Sep 17 AQ4 23.00
5-Sep 17 JY6 26.00
6-Sep 17 ES8 2.00
6-Sep 17 DR5 23.00

So I need the standard deviation of the sum for each day. In this example =stdevp(37,40,0,blank,49,25) but the net sum for 9/3 has to be a blank, not a zero cause it gives you a different result: with a zero 17.19, with the zero blanked out 8.76.
I put this all in a pivot and that works fine, it is getting the zeroes changed to blanks, or just selecting anything over zero for the standard deviation sample. There might be way to do this easily in the Analysis Pack but I will have to install it on Monday morning

Thank you very much

 

[bschwartz]

Can you add a filter to the pivot table to filter out the 0's from that field?

 

[srmorgan]

No, I can't, or at least I don't think I can. If I had a row of zeros or a column of zeroes I could do a filter but otherwise I don't see a way to do it.

Thanks
SRM

 

[oldbrewer]

[TABLE="width: 653"]
<colgroup><col span="7"><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Dept[/TD]
[TD]Charge[/TD]
[TD] Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]WW2[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]AS3[/TD]
[TD="align: right"]21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum of Amount[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]ED4[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]ES3[/TD]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01-Sep[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD="align: right"]03-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]ER4[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02-Sep[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD="align: right"]03-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]ER4[/TD]
[TD="align: right"]-40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03-Sep[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Sep[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04-Sep[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Sep[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05-Sep[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD="align: right"]05-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]AQ4[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06-Sep[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]05-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]JY6[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]153[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]ES8[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Sep[/TD]
[TD="align: right"]17[/TD]
[TD]DR5[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]I used a pivot table to get amounts for each date and then replaced any zeros with blanks[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]do you want the standard deviation of 37,42,49,25 in this example/[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]