Standard Deviation of numbers after a specific row

[gpaletta]

hello. I have a large data set that is pretty much a list of products, when they started being ordered and how much was ordered. So, as you can see in the picture below, there are a lot of zeroes before the values start. This is because the product was not being ordered. So, what I am trying to do is calculate the std dev of the values after and including the first value >0.

For example, row 3. I want to calculate the std deviation of Column N-Q. I need the 0 in column Q because the product has begun to be ordered so anything after the first value>0 needs to be included. As you can see, in column R, I was able to use a Match function to pull the location of the first row that has a value>0, but dont know how I can use that to calculate std deviation of all values after that specific row. Please help me

EXCEL.PNG

[Richard Buttrey]

Hi,

You could use an OFFSET() function anchored on column B, and use the MATCH function for the column offset and then for the width use the number pf columns Less the Match value.

Alternatively an Array formula which excludes the zero values, e.g. =B2:Q2<>0