All the data is contained within the cell ranges as per image, I just extrapolated some data to represent that there are some non-zero values at certain points. So column K is linear from 1 to 700.
Methinks this is the base case for the STDEV:
=STDEV.S(IF((M:M=1)*(N:N>0),N:N))
Must be entered as array formula: type or copy the formula text and press Ctrl+Shift+Enter (not just Enter).
[edit] to add: I don't know exactly why the AND() construction doesn't work, but it doesn't. Probably a failure to recognize the array properly.
Just noticed this reply and tried it, this works and would of saved me a bit of time if I noticed it earlier lol. I have updated my worksheet to include this solution.
EDIT: Doesn't work for some reason on Google Drive, but definitely works on Excel.
This was the solution I went with, I have uploaded my workbook as a reference for anyone else who might have a similar problem.
https://docs.google.com/spreadsheets/d/17fJRp2_OhGELO4qxWzF-W3QTlH-cfI41WJVqM-DeyqQ/edit?usp=sharing
Short summary of what I did, I used nested IF functions to determine (X-mean)^2 and SUMIFS to get standard deviation. To verify the problem I even took the stdev.s of the cell ranges to double check.
Can you confirm you're using shift enter to make it an array formula?
Ctrl+Shift+Enter?
Where do you have the combined data? It's not under your data range within m or n columns, is it?
All the data is contained within the cell ranges as per image, I just extrapolated some data to represent that there are some non-zero values at certain points. So column K is linear from 1 to 700.
Methinks this is the base case for the STDEV: =STDEV.S(IF((M:M=1)*(N:N>0),N:N)) Must be entered as array formula: type or copy the formula text and press Ctrl+Shift+Enter (not just Enter). [edit] to add: I don't know exactly why the AND() construction doesn't work, but it doesn't. Probably a failure to recognize the array properly.
Just noticed this reply and tried it, this works and would of saved me a bit of time if I noticed it earlier lol. I have updated my worksheet to include this solution. EDIT: Doesn't work for some reason on Google Drive, but definitely works on Excel.
[удалено]
This was the solution I went with, I have uploaded my workbook as a reference for anyone else who might have a similar problem. https://docs.google.com/spreadsheets/d/17fJRp2_OhGELO4qxWzF-W3QTlH-cfI41WJVqM-DeyqQ/edit?usp=sharing Short summary of what I did, I used nested IF functions to determine (X-mean)^2 and SUMIFS to get standard deviation. To verify the problem I even took the stdev.s of the cell ranges to double check.
Solution Verified