Code:
--------------------
Function CustomAverage(pRange As Range, pThreshold As Long) As Long

Dim LFirstRow, LLastRow As Integer
Dim LFirstCol, LLastCol As Integer

Dim LCurrentRow As Integer
Dim LCurrentCol As Integer

Dim LTotal As Double
Dim LCount As Integer

On Error GoTo Err_Execute

'Determine first and last row to average
LFirstRow = pRange.Row
LLastRow = LFirstRow + pRange.Rows.Count - 1

'Determine first and last column to average
LFirstCol = pRange.Column
LLastCol = LFirstCol + pRange.Columns.Count - 1

'Initialize variables
LTotal = 0
LCount = 0

'Move through each cell in the range and include in the average
' calculation if the value = pThreshold
For LCurrentCol = LFirstCol To LLastCol
For LCurrentRow = LFirstRow To LLastRow
If Cells(LCurrentRow, LCurrentCol) = pThreshold Then
LTotal = LTotal + Cells(LCurrentRow, LCurrentCol)
LCount = LCount + 1
End If
Next
Next

'Return the average

' check that LCount is not zero and return 0 average if so, this happens in the case
' that all values were below the threshold or null.. which would cause trouble (error)
If LCount = 0 Then
CustomAverage = 0
Else
CustomAverage = LTotal / LCount
End If

On Error GoTo 0

Exit Function

Err_Execute:
CustomAverage = 0
'MsgBox "An error occurred while calculating the Custom Average."

End Function

--------------------


Hi, I was wondering if I could have some help, okay, well the code
above works perfectly, does exactly what I want it to, but it's a bit
buggy, well the spreadsheet is anyway, basically, random numbers will
appear in cells which don't have numbers in them(i.e the custom average
of 4 blank cells will return a "60" as a value, and zeros will appear
where there should be calculations using the custom average function,
i,e the custom average of 60,40, 10 will be zero)
If you click into the cell and press enter, or if you auto fill the
formula (drag it over) from neighbour cells then the calculation will
refresh and will be correct/the zeros will appear/dissapear. obviously
this is a big spreadsheet, and I can't click in every single cell by
hand, is there a way to auto refresh the entire spreadsheet, or
possibly, is there a way to stop it from happening?
If anyone has any ideas then it would be much appreciated
Thanks!


--
ophelia
------------------------------------------------------------------------
ophelia's Profile: http://www.excelforum.com/member.php...o&userid=33778
View this thread: http://www.excelforum.com/showthread...hreadid=535507