I have devised the piece of cade below to calculate avererages based on the last 9 valid values.
Unfortunately i need the code to work backwards throught the range. For example in the rane A1:C10, i need the cells to be checked starting at C10, then b10, a10 then c9, b9, a9, etc.
Hope someone can help otherwise i need to restructure the whole workbook.
Cheers
Damon
***CODE***
Public Function TEST(rg As Range, Optional lCondf As Long = 1) As Long
Dim lTempCount, ltempvalue As Long
Dim cl As Range
For Each cl In rg
If cl.Value <> "NB" Then
lTempCount = lTempCount + 1
ltempvalue = ltempvalue + cl.Value
End If
If lTempCount = 9 Then
GoTo 10
End If
Next cl
10
TEST = ltempvalue / lTempCount
End Function
Unfortunately i need the code to work backwards throught the range. For example in the rane A1:C10, i need the cells to be checked starting at C10, then b10, a10 then c9, b9, a9, etc.
Hope someone can help otherwise i need to restructure the whole workbook.
Cheers
Damon
***CODE***
Public Function TEST(rg As Range, Optional lCondf As Long = 1) As Long
Dim lTempCount, ltempvalue As Long
Dim cl As Range
For Each cl In rg
If cl.Value <> "NB" Then
lTempCount = lTempCount + 1
ltempvalue = ltempvalue + cl.Value
End If
If lTempCount = 9 Then
GoTo 10
End If
Next cl
10
TEST = ltempvalue / lTempCount
End Function