SUMPRODUCT Limitation???

[Jim]

Hi,

I've had a lot os success using SUMPRODUCT for various tasks.
However, I have run into a problem that I cannot seem to resolve.
I have a dataset that is sampled about 277 times a day for a year
(12021) records. I want to reduce this to a set of daily averages.

Column format
A mm/dd/yy hh:mm (date/timestamp with ~5 min. samples)
B 50.0
....
L mm/dd/yy hh:mm (date/timestamp with 24hr averages)


Now I want to get an average value for B over a single day.

The following works, but only for the first 25 days or so
(25*277 > 5301)...

=SUMPRODUCT(($A$2:$A5301>=L3)*($A$2:$A5301<L4)*($B$2:$B5301>-900)*($B$2:$B5301))/SUMPRODUCT(($A$2:$A5301>=L3)*($A$2:$A5301<L4))

When I use the any range larger than about A$2:A$5301 a #NUM! error is
returned in the cell.

=SUMPRODUCT(($A$2:$A6501>=L3)*($A$2:$A6501<L4)*($B$2:$B6501>-900)*($B$2:$B6501))/SUMPRODUCT(($A$2:$A6501>=L3)*($A$2:$A6501<L4))

=SUMPRODUCT(($A$2:$A12021>=L3)*($A$2:$A12021<L4)*($B$2:$B12021>-900)*($B$2:$B12021))/SUMPRODUCT(($A$2:$A12021>=L3)*($A$2:$A12021<L4))


Is there a better way to obtain the average value for column B when
column A (Date) is within a specific range?



Thanks

 

[Peo Sjoblom]

The only way I can think of is if you have a #NUM error in the data
below A5301 regardless whether it is column B or A. If the ranges are not of
the same size you can get it but your example show that they are of the same
size

Press F5, special, select formulas and uncheck everything but errors, click
OK. If they are not formulas
repeat, select constants and errors. Your ranges are definitely within
sumproduct limitations (although for better speed you might want to use a
filter instead)..

--

Regards,

Peo Sjoblom

Hi,

I've had a lot os success using SUMPRODUCT for various tasks.
However, I have run into a problem that I cannot seem to resolve.
I have a dataset that is sampled about 277 times a day for a year
(12021) records. I want to reduce this to a set of daily averages.

Column format
A mm/dd/yy hh:mm (date/timestamp with ~5 min. samples)
B 50.0
...
L mm/dd/yy hh:mm (date/timestamp with 24hr averages)


Now I want to get an average value for B over a single day.

The following works, but only for the first 25 days or so
(25*277 > 5301)...

 

[Ken Wright]

And given that you are OK up till A5301, I would suggest you take a look at the
data in A5302:B5302 first. Having backed up your data, try copying any cell in
the range that works (From Col A) and pasting it over whatever is in A5302 and
then repeat with a value from Col B and paste over B5302. Then try your formula
again in between each paste, increasing them both to A1:A5302 and B1:B5302 etc.
If it now works you obviously have a problem with that data somehow. Like Peo,
I could understand a #VALUE error, but #NUM is strange, and a corresponding
error in that A5302 area should really jump off the page at you (Assumes no
conditional formatting set to hide errors like this).

 

[Jim]

Hi,

After some further investigation I found that there appears to
be a hard limit on the size of the ranges in the SUMPRODUCT function
call. The exact limitation is ranges larger tha 5459 return a #NUM!
error. So, breaking up the desired range into intervals works though
it is slow and is probably less efficient.


=(SUMPRODUCT((A$2:A5399>=M2)*(A$2:A5399<M3)*(C$2:C5399>-900)*(C$2:C5399))

+SUMPRODUCT((A5399:A10399>=M2)*(A5399:A10399<M3)*(C5399:C10399>-900)*(C5399:C10399)))
/
( SUMPRODUCT((A$2:A5399>=M2)*(A$2:A5399<M3))
+SUMPRODUCT((A5399:A10399>=M2)*(A5399:A10399<M3)))

Returns a valid answer

 

[Harlan Grove]

After some further investigation I found that there appears to
be a hard limit on the size of the ranges in the SUMPRODUCT function
call. The exact limitation is ranges larger tha 5459 return a #NUM!
error. So, breaking up the desired range into intervals works though
it is slow and is probably less efficient.


=(SUMPRODUCT((A$2:A5399>=M2)*(A$2:A5399<M3)*(C$2:C5399>-900)*(C$2:C5399))
+SUMPRODUCT((A5399:A10399>=M2)*(A5399:A10399<M3)*(C5399:C10399>-900)
*(C5399:C10399)))/(SUMPRODUCT((A$2:A5399>=M2)*(A$2:A5399<M3))
+SUMPRODUCT((A5399:A10399>=M2)*(A5399:A10399<M3)))

Returns a valid answer

...

Note that you're double counting row 5399.

Maybe you need to do this, but the formula

=SUMPRODUCT((A1:A12000>=F1)*(B1:B12000<=F2)*(C1:C12000=F3)*D112000)

works just fine for me under Excel 97 SR-2.