Ignore Zeros In Standard Deviation

  • I am using the stdev() formula in my spreadsheet that looks back over the last 53 periods. This spreadsheet is updated daily and reflect both the last 53 days, last 53 weeks, and last 53 equivalent days (i.e. Tuesdays) over the last 53 weeks. I want the standard deviation of sales during that time. However, we didn't start tracking some of that information an entire 53 weeks ago, so I have a lot of zero values that are skewing the results. Is there a way to get my standard deviation calculation to ignore zeros? They would seem more important if they actually fall within regular data, but prior to the start date of when we began capturing, the zeros are irrelevant. But as this spreadsheet is auto-updated daily, I need the formula to correctly identify when it should ignore zeros or not.


    Any suggestions?