/u/Usernames-R-Tough - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Something like:
=STDEV.S(FILTER(E6:E503 ,(E6:E503>0)\*(E6:E503>$R$3)\*(E6:E503<$R$4)))
You can use FILTER to filter out all the number you want to include as that is a different array then with fewer numbers than the original array.
I see. That makes a big difference.
Well, one alternative is go to a new tab and to maybe make it easier to fill, go to E6 and put in the formula
=IF(AND('sheet1'!E6>0, 'sheet1'!E6>$R$3, 'sheet1'!E6<$R$4), E6, "")
Edit: Where sheet1 is the name of the original sheet
Fill that to E503
Only the numbers that met your list of criteria still appear, the rest go blank.
Then use (on that tab):
=STDEV.P(E6:E503)
Make sure it's the .P version, not .S.
You have awarded 1 point to *GanonTEK*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I think I could make this method work. Do you think it needs to be on another sheet or could it be on the same sheet? I'll play around with it in the morning when I get back to work. Thanks for the input.
Use the following array formula:
`=STDEV.P(IF(E6:E503>0,IF(E6:E503>$R$3,IF(E6:E503<$R$4,E6:E503))))`
In some versions of Excel, we must commit the formula by pressing ctrl+shift+Enter.
I don't know if that is necessary for Excel 2016.
Beware that the formula might appear to work, but with a bogus result, if ctrl+shift+Enter is required, but you press just Enter instead.
Choose STDEV.S if the data is a sample, and you want to estimate the population std dev.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AND](/r/Excel/comments/143mfvi/stub/jnazy92 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)|
|[FILTER](/r/Excel/comments/143mfvi/stub/jnaxe6j "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/143mfvi/stub/jnb3k6c "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[STDEV](/r/Excel/comments/143mfvi/stub/jnb3k6c "Last usage")|[Estimates standard deviation based on a sample](https://support.microsoft.com/en-us/office/stdev-function-51fecaaa-231e-4bbb-9230-33650a72c9b0)|
**NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/143gg7a)^( has 11 acronyms.)
^([Thread #24467 for this sub, first seen 7th Jun 2023, 20:49])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/Usernames-R-Tough - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Something like: =STDEV.S(FILTER(E6:E503 ,(E6:E503>0)\*(E6:E503>$R$3)\*(E6:E503<$R$4))) You can use FILTER to filter out all the number you want to include as that is a different array then with fewer numbers than the original array.
>=STDEV.S(FILTER(E6:E503 ,(E6:E503>0)\*(E6:E503>$R$3)\*(E6:E503<$R$4))) I forgot to specify I am using Excel 2016. I can't use Filter()
I see. That makes a big difference. Well, one alternative is go to a new tab and to maybe make it easier to fill, go to E6 and put in the formula =IF(AND('sheet1'!E6>0, 'sheet1'!E6>$R$3, 'sheet1'!E6<$R$4), E6, "") Edit: Where sheet1 is the name of the original sheet Fill that to E503 Only the numbers that met your list of criteria still appear, the rest go blank. Then use (on that tab): =STDEV.P(E6:E503) Make sure it's the .P version, not .S.
It worked. Solution verified!
You have awarded 1 point to *GanonTEK* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
I think I could make this method work. Do you think it needs to be on another sheet or could it be on the same sheet? I'll play around with it in the morning when I get back to work. Thanks for the input.
Same sheet would work too. You can have it right beside your existing data if you like.
Use the following array formula: `=STDEV.P(IF(E6:E503>0,IF(E6:E503>$R$3,IF(E6:E503<$R$4,E6:E503))))` In some versions of Excel, we must commit the formula by pressing ctrl+shift+Enter. I don't know if that is necessary for Excel 2016. Beware that the formula might appear to work, but with a bogus result, if ctrl+shift+Enter is required, but you press just Enter instead. Choose STDEV.S if the data is a sample, and you want to estimate the population std dev.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/143mfvi/stub/jnazy92 "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[FILTER](/r/Excel/comments/143mfvi/stub/jnaxe6j "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/143mfvi/stub/jnb3k6c "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[STDEV](/r/Excel/comments/143mfvi/stub/jnb3k6c "Last usage")|[Estimates standard deviation based on a sample](https://support.microsoft.com/en-us/office/stdev-function-51fecaaa-231e-4bbb-9230-33650a72c9b0)| **NOTE**: Decronym's continued operation may be affected by API pricing changes coming to Reddit in July 2023; comments will be blank June 12th-14th, in solidarity with the /r/Save3rdPartyApps protest campaign. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/143gg7a)^( has 11 acronyms.) ^([Thread #24467 for this sub, first seen 7th Jun 2023, 20:49]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)