SumProduct to the rescue again
=SUMPRODUCT((D2:D14-E2:E14)*(D2:D14<0)*(E2:E14<0))/SUMPRODUCT((D2:D14<0)*(E2:E14<0))
Adjust the ranges as necessary; I had my two columns in D and E


--
- K Dales


"RobPaolillo" wrote:


I am trying to write a VBA code that will average the difference of of 2
arrays ignoring the rows where one array is missing data. In the
example below Jul 4 Jul 9th and July 12 and July 14, need to be
ignored. If this can be done in VBA or within a XL formula, any
solution is welcome.

Thanks in advanced.


A B
2-Jul-04 3 4
3-Jul-04 4 5
4-Jul-04 _ 3
5-Jul-04 6 4
6-Jul-04 7 1
7-Jul-04 77 23
8-Jul-04 3 2
9-Jul-04 5 _
10-Jul-04 5 4
11-Jul-04 45 35
12-Jul-04 _ 5
13-Jul-04 1 2
14-Jul-04 1 _

incorrect differece avg -- 5.307692308
correct difference avg -- 7.888888889


--
RobPaolillo
------------------------------------------------------------------------
RobPaolillo's Profile: http://www.highdots.com/forums/m548
View this thread: http://www.highdots.com/forums/t2585993