How to get STDEVP function to only operate on non 0 values

[HooGolfer]

I have a large database of values which includes 0's for entries to ignore. I'd like to be able to calculate the standard deviation on only the non-zero values. Is there an easy way to do this with the current data set (i.e. I'd prefer not to have to copy the entire data set and clear 0 values such that STDEVP works on the resulting copy)?
Thanks,
Steve

[MarvinP]

Hi Steve,

Have you looked at the Stdev.p and stdev.s functions that may do what you need?
http://support.microsoft.com/kb/828103 for just one source.

[HooGolfer]

Hi Marvinp:
The problem is that to those functions, 0 looks like any other valid number. If I had blanks instead of zeros, I think STDEVP would work just fine.
For example, if my data is {0,1,2,3,4,5}, I want the STDEVP to return 1.414, not 1.707. The 0 looks like any other valid number to it. I want it to ignore 0's, but I can't think of a way to do it without copying the entire database or writing a VBA macro.
Thanks,
Steve

[MarvinP]

Can you create a helper column and replace zeros with a space or nothing? Then do your stats on this helper?

[HooGolfer]

MarvinP:
I could, but this is already a big spreadsheet that takes about 6 minutes to calculate. I'd have to create a shadow copy of about 33,000 x 45 cells to do it. That is a fallback plan, but if there is a nifty way of getting the worksheet function to ignore the zeros, I'd prefer that.
Thanks,
Steve

[kvsrinivasamurthy]

Pl try this macro.
The range containg required data to be entered.
The output is stored in D1 Cell as per programme .You pl change as per requiremet.
I have verified for Range A1:B1048576.It does not take any time.
Clarifications welcome.

Please Login or Register  to view this content.

[HooGolfer]

Thanks kvsrinivasamurthy, I'll give it a try.
Steve