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

Using Conditional Sums/Averages on Arrays
 

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" wrote in message
...
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

Using Conditional Sums/Averages on Arrays
 

"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" wrote in message
...
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

Using Conditional Sums/Averages on Arrays
 

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" wrote in message
...
"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" wrote in message
...
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