What is happening with this function

[Aussiebear]

I have come across a web page which shows how to use array functions to count times events but it has left me with little understanding of why it uses "*1" in two of the formulas yet doesn't on another.




Time Started
Before 8am
Between 8am & 8.19am
After 10am


7:35
=Sum((Hour(A2:A10)<8)*1)
=Sum((Hour(A2:A10)=8)*(Minute(A2:A10)>=0)*(Minute(A2:A10)<=19))
=Sum((Hour(A2:A10)>=10)*1)


8:18





8:05





8:07





10:00





10:50





13:55





13:56





14:00





Results
1
3
5




Initially I thought the "*1" in the first & third arrays, was to coerce the True/False to 0/1 but since its not used in the second array then that's probably not true.

[Bob Phillips]

The reason is that the inner function, (HOUR(A2:A10)>=10), creates an array of TRUE/FALSE. SUM cannot add booleans, so it needs to be coerced to numbers, *1 does that, as does+0, or the double unary --. In the other formula, it also creates arrays of TRUE/FALSE, but it uses the * operator on them, and multiplying booleans also coerces them to numbers, TRUE*TRUE = 1, all other combinations resolve to 0. Not sure why it is testing >8 for before 8am, should be =SUM((HOUR(A2:A10)<8)*1).

If you had read my SUMPRODUCT :) page you would know this, it is explained there and applies equally here http://www.xldynamic.com/source/xld.sumproduct.html.

[Bob Phillips]

The middle function looks odd too, seems to be missing a bracket. And test MINUTE >= 0, how can it ever be < 0, waste of time. I would use the simpler and more obvious

=SUM((A2:A10>=--"08:00:00")*(A2:A10<=--"08:19:00"))

or even


=SUMPRODUCT(--(A2:A10>=--"08:00:00"),--(A2:A10<=--"08:19:00"))

[GTO]

Hi Ted,

I believe you also copied the second formula incorrectly, as, as far as I can tell, we need to evaluate the two minute bits against each other.

=SUM((HOUR(A2:A10)=8)*((MINUTE(A2:A10)>=0)*MINUTE(A2:A10)<=19))

By the way, what year is your Excel? I ask only as I used the evaluate function button and stepping thru it helps :-)

Mark

[GTO]

Goodness, some folks are fast typists! (Hi Bob:hi:)

[Aussiebear]

If you had read my SUMPRODUCT :) page you would know this,

ummm... Okay I'll go stand in the corner for a few minutes thinking about what I should have done. :ipray: Just don't whip me no more master.

[Bob Phillips]

Hi Ted,

I believe you also copied the second formula incorrectly, as, as far as I can tell, we need to evaluate the two minute bits against each other.

=SUM((HOUR(A2:A10)=8)*((MINUTE(A2:A10)>=0)*MINUTE(A2:A10)<=19))

By the way, what year is your Excel? I ask only as I used the evaluate function button and stepping thru it helps :-)

Mark

Not quite Mark, the * operator is effectively AND, so you can say HOUR = 8 AND MINUTE > 0 AND MINUTE <= 19, or

=SUM((HOUR(A2:A10)=8)*(MINUTE(A2:A10)>=0)*(MINUTE(A2:A10)<=19))

but as I said the MINUTE(A2:A10>0) test is pointless, there is no such thing as negative time (at least not in our view of the universe).

[GTO]

Ahhh... That took me a bit, but in short, the only reason my wrapping (parenthesis locations) worked out correctly is because (as you mentioned) the second (as written) test is bound to return all TRUE(s). Thus -

=SUM(({FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE})*(({TRUE;TRUE;TR UE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}*MINUTE(A2:A10)<=19))
=SUM(({FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}*({35;18;5;7;0;50 ;55;56;0}<=19))

...works (albeit in an untrustworthy order).

Thank you much; off to bed for this lad. Fighting some crud and not enough sleep...

Mark