Using Conditional Sums/Averages on Arrays

I have two arrays that are 3D. Each dimension is populated within Three tier
For Next loop. The first array has data, the second array has dates
associated with its companion element in the first array:

DataArray1(0,0,0) and DateArray2(0,0,0) are related.

I want to Average and or SUM the column data from:
DataArray1(0,0,0) to DataArray1(1000,100,0)
using a conditional logic on the dates in the DateArray

I want to use something similar to the SUMPRODUCT formula using the unitary
operators "--". I am trying to avoid using Pivot Tables if possible.
Perhaps even a function would work.

Any ideas on how I do this?

Thanks

In VBA, SUMPRODUCT can not be used as an array formula (which is what you
describe). You would need to pass a virtual formula to the evaluate method.
However, it won't work on a 3D array nor will anything slice a 2D array out
of a 3D array as a single command.

That said, looping through the array and building your own result should be
very fast.

--
Regards,
Tom Ogilvy

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I have two arrays that are 3D. Each dimension is populated within Three
tier
> For Next loop. The first array has data, the second array has dates
> associated with its companion element in the first array:
>
> DataArray1(0,0,0) and DateArray2(0,0,0) are related.
>
> I want to Average and or SUM the column data from:
> DataArray1(0,0,0) to DataArray1(1000,100,0)
> using a conditional logic on the dates in the DateArray
>
> I want to use something similar to the SUMPRODUCT formula using the
unitary
> operators "--". I am trying to avoid using Pivot Tables if possible.
> Perhaps even a function would work.
>
> Any ideas on how I do this?
>
> Thanks

"You would need to pass a virtual formula to the evaluate method."

If I were to do this, and my data was in a 2D array, what would this look
like? I have seen the Evaluate Method used with formulas relating to cells.
But as my data is in an array could I pursue the Evalutate method?




"Tom Ogilvy" wrote:

> In VBA, SUMPRODUCT can not be used as an array formula (which is what you
> describe). You would need to pass a virtual formula to the evaluate method.
> However, it won't work on a 3D array nor will anything slice a 2D array out
> of a 3D array as a single command.
>
> That said, looping through the array and building your own result should be
> very fast.
>
> --
> Regards,
> Tom Ogilvy
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> > I have two arrays that are 3D. Each dimension is populated within Three
> tier
> > For Next loop. The first array has data, the second array has dates
> > associated with its companion element in the first array:
> >
> > DataArray1(0,0,0) and DateArray2(0,0,0) are related.
> >
> > I want to Average and or SUM the column data from:
> > DataArray1(0,0,0) to DataArray1(1000,100,0)
> > using a conditional logic on the dates in the DateArray
> >
> > I want to use something similar to the SUMPRODUCT formula using the
> unitary
> > operators "--". I am trying to avoid using Pivot Tables if possible.
> > Perhaps even a function would work.
> >
> > Any ideas on how I do this?
> >
> > Thanks
>
>
>

No. Not unless it were very small any you could write the array out as you
would in a formula in a cell. I was really addressing the use of sumproduct
as an array formula in general. Again, looping through an array is
extremely fast.

--
Regards,
Tom Ogilvy


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> "You would need to pass a virtual formula to the evaluate method."
>
> If I were to do this, and my data was in a 2D array, what would this look
> like? I have seen the Evaluate Method used with formulas relating to
cells.
> But as my data is in an array could I pursue the Evalutate method?
>
>
>
>
> "Tom Ogilvy" wrote:
>
> > In VBA, SUMPRODUCT can not be used as an array formula (which is what
you
> > describe). You would need to pass a virtual formula to the evaluate
method.
> > However, it won't work on a 3D array nor will anything slice a 2D array
out
> > of a 3D array as a single command.
> >
> > That said, looping through the array and building your own result should
be
> > very fast.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have two arrays that are 3D. Each dimension is populated within
Three
> > tier
> > > For Next loop. The first array has data, the second array has dates
> > > associated with its companion element in the first array:
> > >
> > > DataArray1(0,0,0) and DateArray2(0,0,0) are related.
> > >
> > > I want to Average and or SUM the column data from:
> > > DataArray1(0,0,0) to DataArray1(1000,100,0)
> > > using a conditional logic on the dates in the DateArray
> > >
> > > I want to use something similar to the SUMPRODUCT formula using the
> > unitary
> > > operators "--". I am trying to avoid using Pivot Tables if possible.
> > > Perhaps even a function would work.
> > >
> > > Any ideas on how I do this?
> > >
> > > Thanks
> >
> >
> >