Averaging formula (2000 SR-1)

[ptebo]

Is there a way to average just the last 4 active cells in a row when the row might contain several blank cells in various locations? (i.e. in one row, you might have to go back 6 cells to get the last 4 active cells; in another row, all 4 of the last cells might be active; in yet another row, you have to go back 10 cells before you finally get 4 of them that are active)

[sdckapr]

I think the easiest way is with a custom function:

<pre>Function AvgLast(rng As Range, iNumber As Integer)
Dim x As Long
Dim lCount As Long
Dim dSum As Double
lCount = 0
dSum = 0
x = rng.Cells.Count
Do While x > 0 And lCount < iNumber
If Not IsEmpty(rng.Cells(x)) Then
If IsNumeric(rng.Cells(x).Value) Then
lCount = lCount + 1
dSum = dSum + rng.Cells(x).Value
End If
End If
x = x - 1
Loop
If lCount < iNumber Then
AvgLast = CVErr(xlErrNA)
Else
AvgLast = dSum / lCount
End If
End Function</pre>


Use it like:
<pre>=AvgLast(A1:P1,4)</pre>


to get the average of the 4 last values in the range A1-P1. If there are <4 values then it will give a #NA Error. If you want get the average of whatever is there (even if < the number you give) replace:
<pre> If lCount < iNumber Then
AvgLast = CVErr(xlErrNA)
Else
AvgLast = dSum / lCount
End If</pre>


with just:

<pre> AvgLast = dSum / lCount</pre>


Or you can replace the error with something else. cverr(xlErrNum) will give a #Num error (for example)

I kept the formula "generic" having you fill in the number to "count". You could make it optional with the 4 as default, or even hard code it if desired, to eliminate the need to enter the 2nd parameter.

Steve