I am trying to use a specific entry in excel that will find all matches in a column and calculate the standard deviation of the range for data in another column that match. Let's say I have the entry in cell K3 called Day of Interest. In K4 I have the data I want to be found in this case "D0". In a given set of data I want to match all entries in one column that have "D0" and calculate the standard deviation of their concentrations from another column. If you look at column 2 the issue I'm running into is I cannot figure out how to have the formula look for a range of values given my entry. I want the cell I input D0 into to be found in another table and the stddev to be calculated. I am not sure how to generate the range in the formula.
Table1
| Day | Concentration |
|---|---|
| D0 | 22 |
| D0 | 40 |
| D7 | 50 |
| D7 | 25 |
| D8 | 35 |
| D9 | 50 |
Below is what I have tried in excel.
Table 2
| Day of Interest | Standard Deviation |
|---|---|
| D0 | =If(K3="","",(stddev.p('insert a range here based on K3' |
CodePudding user response：
The FILTER function helps here.
The formula is =IF(K3="","",STDEV.P(FILTER(Table1[Concentration],Table1[Day]=K3)))
FILTER creates an array based on a condition being true.