VBA code for averaging a range of cells
 

can anyone suggest me the fastest code (i have a lot of data in the
sheet) to accomplish these:

say that in range A1:A1000 I have either numbers or #N/A.
say that in range B1:B1000 I have either true or false.

I need a formula whose interface would be:

=custom_average(A1:A1000;B1:B1000)

that would return the arithmetic average of those values in the range
A1:A1000 where the value in the range A1:A1000 is not #N/A and where
the same element in the range B1:B1000 (es A1 Vs B1 , A2 vs B2 ... etc
etc) is true.

in plain english I would:

1) get the range 1 (first parameter)
2) remove from the array all #N/A
3) do something like sumproduct(A1:A1000*B1:B1000)

but how to do all this in VBA ?

Thanks in advance for any help


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=569797

VBA code for averaging a range of cells
 

Function Custom_Average(rng As Range)
Dim cell As Range
For Each cell In rng
If Not IsError(cell.Value) Then
If cell.Offset(0, 1).Value Then
Custom_Average = Custom_Average + cell.Value
End If
End If
Next cell

End Function


but what is wrong with worksheet function I gave you in the other thread?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kayard" wrote in
message ...

can anyone suggest me the fastest code (i have a lot of data in the
sheet) to accomplish these:

say that in range A1:A1000 I have either numbers or #N/A.
say that in range B1:B1000 I have either true or false.

I need a formula whose interface would be:

=custom_average(A1:A1000;B1:B1000)

that would return the arithmetic average of those values in the range
A1:A1000 where the value in the range A1:A1000 is not #N/A and where
the same element in the range B1:B1000 (es A1 Vs B1 , A2 vs B2 ... etc
etc) is true.

in plain english I would:

1) get the range 1 (first parameter)
2) remove from the array all #N/A
3) do something like sumproduct(A1:A1000*B1:B1000)

but how to do all this in VBA ?

Thanks in advance for any help


--
kayard
------------------------------------------------------------------------
kayard's Profile:
http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=569797