Excel/insert column after every third data column and calculate CV (STDEV/AVERAGE), then calculate average of CVs for that column

Question

I analyze numerical data in Excel spread sheets, each row contains data which is in triplicate one column after another (left to right, see Excel data set picture). For each row there can be 30-180 columns total (so 10-60 samples in triplicate per row). In addition, there can be 10-180 rows of the data. I want the VB code to insert a column after every third column (starting with column D) and in that new column calculate the CV [=STDEV(D3:F3)/AVERAGE(D3:F3)] for the row values across the three columns (D3/E3/F3), and at the bottom of the inserted column calculate the average of all the CVs for that column (see Processed Excel data set picture). I also want the code to recognize when it has come to the end of the columns "with data" since my data sets have variable numbers of columns (but always in groups of 3) and I also want the code to recognize when it has come to the end on the rows "with data" since the number of rows also varies. Below are pictures of the starting Excel data set and the Processed Excel data set. I looked at many related answered questions that came up in the search, but none fit this application, and I have very little knowledge of writing code. Thank you for the help, it is really appreciated.

Excel data set

Processed Excel data set

What are your 'rules' for 'end of data'? An blank/empty cell value in a row or column? — Wayne G. Dunn
Hi Wayne, thanks for looking at this, my end of data rule is a "blank/empty cell value in row or column" — Bill

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2016-11-14T20:45:41

Try the following code... just change the sheet name to whatever sheet name you have.

Option Explicit

Function STDEV_AVG()
Dim ws      As Worksheet
Dim iRow    As Integer
Dim iCol    As Integer
Dim iLastrow    As Long
Dim iLastCol    As Long
Dim rng1        As Range

    Set ws = ActiveWorkbook.Sheets("sheet5")
    Sheets(ws.Name).Activate
    With ws
        iLastrow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        iLastCol = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
    End With

    If iLastCol < 6 Then
        MsgBox "There must be a minimum of 6 columns, else this format seems incorrect!"
    End If

    For iCol = iLastCol + 1 To 7 Step -3
        ws.Cells(1, iCol).Select
        ActiveCell.Offset(1).EntireColumn.Insert (xlShiftToRight)
        Cells(2, iCol) = "StdDev"
        For iRow = 3 To iLastrow
            If Cells(iRow, iCol - 1) <> "" Then
                Set rng1 = ws.Range(Cells(iRow, iCol - 3), Cells(iRow, iCol - 1))
                'ws.Range(Cells(iRow, iCol - 3), Cells(iRow, iCol - 1)).Select
                Cells(iRow, iCol).Formula = "=STDEV.P(" & rng1.Address & ")"
            End If
        Next iRow
        Set rng1 = ws.Range(Cells(3, iCol), Cells(iLastrow, iCol))
        'ws.Range(rng1.Address).Select
        Cells(iLastrow + 1, iCol).Formula = "=Average(" & rng1.Address & ")"
    Next iCol

End Function

Excel/insert column after every third data column and calculate CV (STDEV/AVERAGE), then calculate average of CVs for that column

1 Answers