Using Conditional Sums/Averages on Arrays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelMonkey - 23 Feb 2006 11:46 GMT

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

Reply to this Message

Tom Ogilvy - 23 Feb 2006 13:17 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 13 lines]
>
> Thanks

Reply to this Message

ExcelMonkey - 23 Feb 2006 14:04 GMT

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

> 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.
[quoted text clipped - 23 lines]
> >
> > Thanks

Reply to this Message

Tom Ogilvy - 23 Feb 2006 14:20 GMT

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.

Signature

Regards,
Tom Ogilvy

> "You would need to pass a virtual formula to the evaluate method."
>
[quoted text clipped - 29 lines]
> > >
> > > Thanks

Reply to this Message