[SOLVED] VBA - averaging the difference of 2 arrays, ignoring blanks

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

Hi Rob,

There may be an easier way, but here's an array formula that should work for
you:

=SUM(((B1:B13)>0)*((C1:C13)>0)*((B1:B13)-(C1:C13)))/SUM(((B1:B13)>0)*((C1:C13)>0))

Since it's an array formula, you must enter it with Ctrl+Shift+Enter.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


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

=SUMPRODUCT(--(B1:B13<>""),--(C1:C13<>""),B1:B13-C1:C13)/SUMPRODUCT(--(B1:B1
3<>""),--(C1:C13<>""))

--
Regards,
Tom Ogilvy


"RobPaolillo" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

Works great. Thanks!


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