Search code examples
excelvbamacostextstandard-deviation

Calculate standard deviation of same text values between cells in same column

The last few days I have been trying to create a macro in Excel which can calculate standard deviation which will take values from column B of same text values between different cells in column A and give the result in corresponding cell in column C. In my sheet there are lots of different text values in column A and corresponding value in column B. I want to take all values of column B from first row to last row of corresponding same text in column A and calculate the standard deviation of these values and shows the result in column C.

As an example, when it starts from first cell (aass) then it has to count all aass and take values from corresponding cell of column B after that calculate standard deviation. Please help me out.I will be happy to hear from you guys. I am trying to show how my sheet which will look like as below: 

column A  column B  column C
text       value    result(standard deviation)
aass       112      35.16
aadd       243      12.9
ffdd       523      108.55
aass       198      35.16
aadd       252      12.9
aass       136      35.16
ffdd       342      108.55
ffdd       312      108.55
aadd       222      12.9
aadd       255      12.9
ffdd       322      108.55
aass       112      35.16
jjhgf      487      25.8
pouwe      565      6
jjhgf      451      25.8
jjhgf      424      25.8
qwert      643      0
pouwe      553      5
qwert      643      0
.....      ....    ...
.....      ....    ...
Solution

  • I will show you what I mean by a Table with structured references. I copied your data for the text and value columns. I also added the header: Result to cell C1.

    I then selected the Table option from the Insert ribbon (on the Tables tab)

    Insert Table

    I then entered this formula in C2 as an array formula, confirming it by holding down ctrl+shift while hitting Enter

    =STDEVP(IF(Table1[[#This Row],[text]]=[text],[value],""))

    Excel automagically copied that formula down to the last row and produced the requested results. If you now add a row, the table will extend (including the formula). You can also add columns with different formulas.

    In the formula above, Table1[[#This Row],[text]] refers to the context of the text column only on the row containing the formula, and [text] by itself refers to the entire text column; [value] refers to the entire value column.

    The autoextension, and the ability to use the actual column labels is a desireable feature of Excel tables and structured references. It is similar to having dynamic named ranges, but easier to implement.

    enter image description here