Argento_Nuclear

New Member
Joined
Apr 5, 2019
Messages
5
I am trying to find the standard deviation for data presented in different ranges and I am currently using the following equation:
=+STDEV.S(IF((e7a1!A12:A1010>=Primary!K$5)*e7a1!A12:A1010<=Primary!K$6);INDEX(Table1;0;MATCH($D19;Table1[#Headers];0));))

The reason for the formatting is that its for data across a time span based on a column name.

However, I am not getting the right answer out of this as when I compare it to:
=STDEV.S(e7a1!JM49:JM69)

Using the second option where I am manually selecting the same range I am trying to select using equation 1, I am getting the correct answer where as using equation 1 I do not.

I would appreciate the help.

Thanks.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,966
Office Version
  1. 2010
Platform
  1. Windows
I suspect that you want to write:

Rich (BB code):
=STDEV.S(IF((e7a1!A12:A1010>=Primary!K$5)*(e7a1!A12:A1010<=Primary!K$6);INDEX(Table1;0;MATCH($D19;Table1[#Headers];0)))

The key is the addition of the red-highlighted left-parenthesis, balanced by removing a right-parenthesis at the end.

Also note the omission of the "+" after "=". It serves no useful purpose.

You might consider STDEV.P instead of STDEV.S. STDEV.P is the actual std dev of the data. STDEV.S is the approximate std dev of a larger "population", assuming that your data is a "sample".

Lastly, you might consider wrapping IFERROR around the STDEV function, in case there is not at least two rows of data that meet the conditions. Something like:

Rich (BB code):
=IFERROR(STDEV.S(IF((e7a1!A12:A1010>=Primary!K$5)*(e7a1!A12:A1010<=Primary!K$6);INDEX(Table1;0;MATCH($D19;Table1[#Headers];0))), "")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top