convert =IF(ISERROR(AVERAGE(V10:V13)),"",AVERAGE(V10:V13)) to a function

  • Thread starter Thread starter Niki Odolphi
  • Start date Start date
N

Niki Odolphi

I want to change a formula to a VBA function. So instead of:

=IF(ISERROR(AVERAGE(V10:V13)),"",AVERAGE(V10:V13))


I want to use:
=myaverage(V10:V13)

Any ideas ? I have tried just converting this to a function almost
word for word and it still doesn't work.

Function monthavg(monthfigures)
If IsError(Application.WorksheetFunction.Average(monthfigures)) Then
monthavg = ""
Else
monthavg = Application.WorksheetFunction.Average(monthfigures)
End If
End Function
 
Hi
try
Function monthavg(monthfigures as range)
dim ret_value
on error resume next
ret_value = application.worksheetfunction.average(monthfigures)
if err.number <>0 then
ret_value = ""
end if
on error goto 0
monthavg = ret_value
end function
 
when you use a user-defined function as a formula for a
cell, to get it to update you need to put
Application.Volatile in the function. This makes the
function execute each time the spreadsheet is updated.
 
Here is the real answer.

Worksheetfunction.Average raises a trappable error. This can't be tested
with IsError.

Application.Average returns the sheet style error that can be tested with
iserror. So the below works:

Function monthavg(monthfigures)

If IsError(Application.Average(monthfigures)) Then
monthavg = ""
Else
monthavg = Application.Average(monthfigures)
End If
End Function


Otherwise you would need an error trap something like this

Function monthavg(monthfigures)
On Error goto ErrHandler
monthavg = Application.WorksheetFunction.Average(monthfigures)
End If
exit function
ErrHandler:
monthAvg = ""
End Function
 
Thank you all for your help. The one that done exactly what I wanted was:

Function monthavg(monthfigures)

If IsError(Application.Average(monthfigures)) Then
monthavg = ""
Else
monthavg = Application.Average(monthfigures)
End If
End Function


Your help is very much appreciated while I get to grips with VBA.
 
This is slightly more efficient:


Public Function monthavg(ByRef monthfigures As Range) As Variant
monthavg = Application.Average(monthfigures)
If IsError(monthavg) Then monthavg = ""
End Function

This way the average only has to be calculated once.
 
Back
Top