Sumproduct problems 1

[ADE6]

Hi

I am using the two SUMPRODUCT formulas below to find how many times positive or negative values(shown in column BJ)appear at certain times of day.Times are located in column BE.

I have repeated the formula to perform the same test for every hour of the day.

These formulas worked when i used them in another sheet to calculate months that showed positive or negative values.

I can't figure out why they are not working.The formula are just returning zero but i know there are a combination of positive and negative values.



=SUMPRODUCT((BE2:BE300="00:00")*(BJ2:BJ300>0))
=SUMPRODUCT((BE2:BE300="00:00")*(BJ2:BJ300<0))


BE-----------------------------------------------------BJ
09:00 1.8382 1.8394 1.8373 1.8378 -0.0004
10:00 1.8378 1.8383 1.835 1.8358 -0.0020
11:00 1.8358 1.8385 1.8352 1.8384 0.0026
12:00 1.8384 1.8429 1.8377 1.8413 0.0029
13:00 1.8413 1.8423 1.8408 1.8414 0.0001
14:00 1.8414 1.8415 1.8396 1.8396 -0.0018
15:00 1.8396 1.8406 1.8396 1.8405 0.0009
16:00 1.8405 1.8418 1.8404 1.8413 0.0008
17:00 1.8413 1.8413 1.8393 1.8406 -0.0007
18:00 1.8406 1.841 1.8405 1.8408 0.0002
19:00 1.8408 1.841 1.8405 1.8405 -0.0003
20:00 1.8405 1.8411 1.8377 1.8381 -0.0024
21:00 1.8381 1.8384 1.8369 1.8374 -0.0007
22:00 1.8374 1.8393 1.8374 1.839 0.0016
23:00 1.839 1.8397 1.8374 1.8391 0.0001
00:00 1.8391 1.8395 1.8379 1.839 0.0000
01:00 1.839 1.8415 1.839 1.8414 0.0024
02:00 1.8414 1.8441 1.8404 1.8407 -0.0007
03:00 1.8407 1.8448 1.8405 1.8448 0.0041
04:00 1.8448 1.8454 1.8434 1.8439 -0.0009
05:00 1.8439 1.8447 1.8436 1.844 0.0001
06:00 1.844 1.8442 1.8417 1.842 -0.0020
07:00 1.842 1.8421 1.8377 1.838 -0.0040
08:00 1.838 1.8408 1.838 1.8397 0.0017

Thanks for the help

Ade

 

[SkipVought]

Ade,

Column BE Values are NOT "00:00", "01:00" That's TEXT. These are TIME values. What is formatted to DISPLAY as "00:00" has a VALUE of ZERO. "01:00" is 1/24.

So if you SUBSTITUTE numeric equivalents for each TIME VALUE, your SUMPRODUCTS will work like a charm!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[johnnygeo]

Just an additional observation: you are returning only positive and negative counts, not zero values. The sample data you posted would correctly evaluate to 0 for both formulae, even after you follow Skip's advice.

 

[SkipVought]

Ade,

I noticed another thing that bothered me and I failed to mention to you.

Your Time Values start with 09:00 and end with 08:00.

I know what you are trying to portray, and it may not bite you in this instance. But crossing a DAY BOUNDARY with Time Values can be a problem IF you are testing that one Time Value is GREATER THAN another.

By implication, it SEEMS that 08:00 is the GREATEST Time Value. But, in fact, it is not.

If the intent were to be able to state that the Time Values are in ascending order, then the time values AFTER 23:00 would need to be incrimented by ONE.

No extra change! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ADE6]

It all seems to work ok now by displaying time values using the 1/24 format.

Zero values have been accounted for using a separate formula.

Thanks for all the ideas

Ade