STDEV to Ignore Blanks/0's

Hello!

I have done a search through the forums and found a variety of formula for a variety of STDEV issues, but none as mundane as the problem I am having.

What I have:
I have columns of numbers by employee, with each row being a day of the month. Each cell pulls the total number of samples they have processed during their shift (a separate formula). Some cells return nothing because they were not on shift that day.

What I need:
I need to calculate the Standard Deviation that ignores blank cells and 0's that I can apply to each employee.

This is what I have tried:
STDEV for Excel.jpg

I also tried selecting individual cells that have numbers with "=STDEV(I2,I8,I9,I15,I16,I17)", which gives 11.78134118. I would prefer something I did not have to manually plot, to elimate errors on my part (the 7.7... results is from "=STDEV(I2:I32)"). NOTE: They are all entered as array.

I would greatly appreciate any help!

I suggest you try =STDEVA(IF(I2:I32>0,I2:I32))
Enter as an array formula, and see if it gives the correct result.

I just used some trial and error on all the STDEV options, and found that this one seemed to set blanks to zero which are then ignored by the IF part of the formula.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David,

That did it! Thank you very much! I can't believe I couldn't get something similar to work before.