MrExcel.com - Your One Stop for Excel Tips & Solutions

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

Posted by Joe on April 06, 2001 3:23 PM

Artem,
How about something like this?

Function MyAverage(Tgl1, Tgl2, Tgl3, Number1, Number2, Number3)
If Number1 = 0 Then Tgl1 = 0
If Number2 = 0 Then Tgl2 = 0
If Number3 = 0 Then Tgl3 = 0
MyAverage = (Tgl1 * Number1 + Tgl2 * Number2 + Tgl3 * Number3) / (Tgl1 + Tgl2 + Tgl3)
End Function

Posted by Joe on April 06, 2001 3:29 PM

Oops! I should read the questions more carefully. This should work.

Function MyAverage(Tgl1, Tgl2, Tgl3, Number1, Number2, Number3) As Variant
If Number1 = 0 Then Tgl1 = 0
If Number2 = 0 Then Tgl2 = 0
If Number3 = 0 Then Tgl3 = 0
If (Tgl1 + Tgl2 + Tgl3) = 0 Then
MyAverage = 0
Else
MyAverage = (Tgl1 * Number1 + Tgl2 * Number2 + Tgl3 * Number3) / (Tgl1 + Tgl2 + Tgl3)
End If
End Function

: hello people, : Number1 = 0, Number2 = 500, Number1 = 0 : 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") : Var1 = Application.WorksheetFunction.SumIf(Number2, "<>0") + _ : Application.WorksheetFunction.SumIf(Number3, "<>0") : : Var2 = Application.WorksheetFunction.CountIf(Number2, "<>0") + _ : Application.WorksheetFunction.CountIf(Number3, "<>0") : Var1 = Application.WorksheetFunction.SumIf(Number1, "<>0") + _ : Application.WorksheetFunction.SumIf(Number3, "<>0") : : Var2 = Application.WorksheetFunction.CountIf(Number1, "<>0") + _ : Application.WorksheetFunction.CountIf(Number3, "<>0") : Var1 = Application.WorksheetFunction.SumIf(Number1, "<>0") + _ : Application.WorksheetFunction.SumIf(Number2, "<>0") : : Var2 = Application.WorksheetFunction.CountIf(Number1, "<>0") + _ : Application.WorksheetFunction.CountIf(Number2, "<>0") : Var1 = Application.WorksheetFunction.SumIf(Number1, "<>0") : Var1 = Application.WorksheetFunction.SumIf(Number2, "<>0") : Var1 = Application.WorksheetFunction.SumIf(Number3, "<>0") : ValueOut = 0 : End If : MyAverage = ValueOut : End Function


Posted by Artem on April 06, 2001 4:17 PM

THANKS!!!

Thanks a lot Joe! Of course it works now. And the code itself is quite impressive. Really cool. Thanks!