Range of formula changes according to a condition

[tsakta13ole]

Hi. The formula STDEV.P(B2:$B$22) calculates the standard deviation of the range b2:b22. This formula works great until row 21. The problem is that in row 22 I would like it to calculate the stdev of range (B22:$B$42) and not B22:B22.
Now, I will try to explain where B42 comes from. Column E has dates. Between E2 and E21 there is the same date. It changes in E22 and stays the same until E41. It changes again in E42. So every time the date in cell E changes I would like this to be the new end row of the range B2:$B$X, where X is the row that the date in cell E changes.
A table to understand. G is the end result
row\column B E G
2 11 14/2/2006 =STDEV.P(B2:$B$22)
3 12 14/2/2006 =STDEV.P(B3:$B$22)
4 10 14/2/2006 =STDEV.P(B4:$B$22)
... 14/2/2006
21 13 14/2/2006 =STDEV.P(B21:$B$22)
22 14 14/3/2006 =STDEV.P(B22:$B$42)
... 14/3/2006
41 11 14/3/2006 =STDEV.P(B41:$B$42)
42 10 18/4/2006 =STDEV.P(B42:$B$67)
G42 has $B$67 because in E67 the date is not 18/4/2006.

Hope you understand what I'm trying to do!
Thanks for any help!

P.S.: I have also posted this problem here https://www.mrexcel.com/forum/excel-...condition.html

[José Augusto]

Try this formula

Please Login or Register  to view this content.

[leelnich]

Hi all- Perhaps this ARRAY FORMULA in G2:

Please Login or Register  to view this content.

[tsakta13ole]

Try this formula

Please Login or Register  to view this content.

Thank you for your effort Jose. The problem is that your formula for example in row 7 computes stdev.p of B2:B7 not B7:B22. Do you know how can I fix it please?

[tsakta13ole]

Hi all- Perhaps this ARRAY FORMULA in G2:

Please Login or Register  to view this content.

Thank you leelnich! Your formula finds the stdev of the data of the row I'm in until 1000. The problem is that 1000 needs to change.

[leelnich]

My first effort (post#3) handled row 1 incorrectly, but this ARRAY FORMULA* should work:

Please Login or Register  to view this content.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

...The problem is that 1000 needs to change.

If E1000 has a date, B1000 is part of the final range. Obviously, row 1000 is an arbitrary choice and may be adjusted to suit.

[José Augusto]

I suppose this is what you want

Please Login or Register  to view this content.

(first formula not work well)
Try that

Please Login or Register  to view this content.

[tsakta13ole]

Thanks for your effort guys! It's none of the above. Sorry. I guess I'm not explaining it well. I will do it manually. It's 10 years but I will be patient!

[José Augusto]

Hi
Please, see the file and watch me what is wrong.

[leelnich]

One last try at a single-column ARRAY FORMULA. Paste in G2 and copy down:

Please Login or Register  to view this content.

[leelnich]

If single formulas don't work, try a helper column (K in this example). Copy from E2 down to cell ABOVE last entry. Select K3 (one cell lower) and paste. Change K2 to match K3. Then paste this ARRAY FORMULA in G2 and copy down:

Please Login or Register  to view this content.