# Average custom function - PLEASE HELP!

Posted by Artem on April 06, 2001 2:43 PM

hello people,

I am trying to do a custom function to average 3 numbers. It's not a straightforward average though, but with a few things:

1. The function also depends on 3 toggles (basically cells having either 1 or 0) each saying if its corresponding number should be included or excluded from the average. For examble, If Tgl1 is 1, Tgl2 is 0, and Tgl3 is 1 the function will find the average of Number1, and Number3.

2. I want to exclude zero numbers from being included into an average no matter what their toggle is.

Below is my function which is quite amateur, and almost works. The problem is - it returns #VALUE! in situations like:

Tgl1 = 1, Tgl2 = 0, Tgl3 = 1 and

Number1 = 0, Number2 = 500, Number1 = 0

How can I trap this? My Excel is 97. THANK YOU!!!

Here's what I have:

Function MyAverage(Tgl1, Tgl2, Tgl3, Number1, Number2, Number3)

Dim ValueOut As Double

Dim Var1 As Double

Dim Var2 As Double

If Application.WorksheetFunction.And(Number1 = 0, Number2 = 0, Number3 = 0) = True Then

ValueOut = 0

ElseIf Application.WorksheetFunction.And(Tgl1 > 0, Tgl2 > 0, Tgl3 > 0) = True Then

Var1 = Application.WorksheetFunction.SumIf(Number1, "<>0") + _

Application.WorksheetFunction.SumIf(Number2, "<>0") + _

Application.WorksheetFunction.SumIf(Number3, "<>0")

Var2 = Application.WorksheetFunction.CountIf(Number1, "<>0") + _

Application.WorksheetFunction.CountIf(Number2, "<>0") + _

Application.WorksheetFunction.CountIf(Number3, "<>0")

ValueOut = Var1 / Var2

ElseIf Application.WorksheetFunction.And(Tgl1 <= 0, Tgl2 > 0, Tgl3 > 0) = True Then

Var1 = Application.WorksheetFunction.SumIf(Number2, "<>0") + _

Application.WorksheetFunction.SumIf(Number3, "<>0")

Var2 = Application.WorksheetFunction.CountIf(Number2, "<>0") + _

Application.WorksheetFunction.CountIf(Number3, "<>0")

ValueOut = Var1 / Var2

ElseIf Application.WorksheetFunction.And(Tgl1 > 0, Tgl2 <= 0, Tgl3 > 0) = True Then

Var1 = Application.WorksheetFunction.SumIf(Number1, "<>0") + _

Application.WorksheetFunction.SumIf(Number3, "<>0")

Var2 = Application.WorksheetFunction.CountIf(Number1, "<>0") + _

Application.WorksheetFunction.CountIf(Number3, "<>0")

ValueOut = Var1 / Var2

ElseIf Application.WorksheetFunction.And(Tgl1 > 0, Tgl2 > 0, Tgl3 <= 0) = True Then

Var1 = Application.WorksheetFunction.SumIf(Number1, "<>0") + _

Application.WorksheetFunction.SumIf(Number2, "<>0")

Var2 = Application.WorksheetFunction.CountIf(Number1, "<>0") + _

Application.WorksheetFunction.CountIf(Number2, "<>0")

ValueOut = Var1 / Var2

ElseIf Application.WorksheetFunction.And(Tgl1 > 0, Tgl2 <= 0, Tgl3 <= 0) = True Then

Var1 = Application.WorksheetFunction.SumIf(Number1, "<>0")

ValueOut = Var1

ElseIf Application.WorksheetFunction.And(Tgl1 <= 0, Tgl2 > 0, Tgl3 <= 0) = True Then

Var1 = Application.WorksheetFunction.SumIf(Number2, "<>0")

ValueOut = Var1

ElseIf Application.WorksheetFunction.And(Tgl1 <= 0, Tgl2 <= 0, Tgl3 > 0) = True Then

Var1 = Application.WorksheetFunction.SumIf(Number3, "<>0")

ValueOut = Var1

Else

ValueOut = 0

End If

MyAverage = ValueOut

End Function