```
Option Explicit
Sub avg()
Dim lrow As Long
Dim rng, cell, rng1, cell1 As Range
Dim i, j As Variant
Dim sh As Worksheet
'**************************************************************************************
' set your sheet variable (this is down and dirty so i used activesheet - don't
'**************************************************************************************
Set sh = ActiveSheet
With sh
'**************************************************************************************
' Find last row of data (substitute "A" with what ever column letter has your
' complete data in it
'**************************************************************************************
lrow = Range("A" & .Rows.Count).End(xlUp).Row
'**************************************************************************************
' set rng range variable to the cell I want my average value to appear in
'**************************************************************************************
Set rng = Range(.Cells(1, 3), .Cells(lrow, 3))
'**************************************************************************************
' FOR EACH loop to move through each cell in my rng range
'**************************************************************************************
For Each cell In rng
'**************************************************************************************
' set rng1 to the cell one column to the left of the active cell in rng
' set cell1 to the cell two columns to the left of the active cell in rng
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' if you need more columns then set your ranges to include the values
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
'**************************************************************************************
Set rng1 = Cells(cell.Row, cell.Offset(, -1).Column)
Set cell1 = Cells(cell.Row, cell.Offset(, -2).Column)
'**************************************************************************************
' if both rng1 and cell1 are empty do nothing
'**************************************************************************************
If rng1.Value = "" And cell1.Value = "" Then
Else
'**************************************************************************************
' if one or the other is empty then simply divide the non-empty cell by two
'**************************************************************************************
If rng1.Value = "" Or cell1.Value = "" Then
If rng1.Value = "" Then
'**************************************************************************************
' set your sheet variable (this is down and dirty so i used activesheet - don't
'**************************************************************************************
cell.Formula = WorksheetFunction.Sum(cell1 / 2)
Else
cell.Formula = WorksheetFunction.Sum(rng1 / 2)
End If
Else
'**************************************************************************************
' when both are occupied get your average
'**************************************************************************************
cell.Formula = WorksheetFunction.Average(rng1, cell1)
End If
End If
Next cell
End With
End Sub
```