What does this mean?

[Andrew Chalk]

In the formula below, what does the asterisk '*' mean?

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))


Many thanks

 

[Chip Pearson]

It serves as an "AND" condition. In the formula,

(A2:A500="5/27/2005")
and
(E2:E500="5098")

return arrays of TRUE or FALSE values, each indicating the result
of the comparison. So, for example, you'll get two arrays like

{TRUE, TRUE, FALSE, .....} and {FALSE, TRUE, TRUE, ....}

The multiplication operator * multiplies these two arrays
together; each element in the first array is multiplied by the
corresponding element in the second array, and the result is an
array of these products.

Since Excel treats TRUE as 1 and FALSE as 0, the resulting
product of the multiplication will be 1 (or TRUE) only when both
operands are TRUE (or 1).

So, using the example arrays above, the result of the
multiplication is

(0, 1, 0, ...)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[William Benson]

Is it an array function? (Are there braces around it?) If so, it means kinda
like an additional condition follows...
example

Col

Row C D
5 Bill 10
6 Tom 50
7 Bill 100
110 {=SUM((C5:C7="Bill")*(D57))}


shows the sum of items in column D (the cells indicated) when items in
Column C (the cells indicated) are = value "Bill"

You have to enter a formula like this using Ctrl-Shift-Enter, not just
Enter.

HTH,

Bill

 

[Harlan Grove]

In the formula below, what does the asterisk '*' mean?

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

It means a logical AND that would give a formula result. Excel's AND
function returns only a single result, so

AND({FALSE;FALSE;TRUE;TRUE},{FALSE;TRUE;FALSE;TRUE})

returns FALSE rather than {FALSE;FALSE;FALSE;TRUE} (the pairwise AND result
for the two arrays). The standard way of doing a pairwise AND involves using
arithmetic multiplication. TRUE is converted to 1 and FALSE to 0, so

{FALSE;FALSE;TRUE;TRUE}*{FALSE;TRUE;FALSE;TRUE}

becomes

{0;0;1;1}*{0;1;0;1}

which evaluates to {0;0;0;1}, and when that result is used as the first
argument to IF, 0 is treated as FALSE and nonzero (so 1) as TRUE.

 

[Andrew Chalk]

OK. A cross-product in matrix parlance?

Thanks for the detail. Same goes for QWilliam and Harlan's replies.

- Andrew

 

[William Benson]

Interesting analogy, I guess it may be a "special case" of a matrix product
.... however, whereas matric arithmetic doesn't care if the two arrays (or
vectors) are of the same size, Excel does (at least in this case, so far as
I know). It fails if each array does not have the same number of elements.

 

[Bill Ridgeway]

As a matter of curiosity what is the difference between

=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500))

=COUNT(IF(AND(A2:A500="5/27/2005",E2:E500="5098"),E2:E500))
(assuming you can have count/if/and)

Regards.

Bill Ridgeway
Computer Solutions

 

[William Benson]

Apples and oranges... Array multiplication versus simply evaluating a
single, condition which just
happens to involve arrays.

If all items in A2:A500 do in fact = "5/27/2005", AND
all items in E2:E500 do in fact = "5098", your answer will be = 499
(provided you enter the formula as an array -- otherwise you get an error).

If EITHER not all items in A2:A500 = "5/27/2005", OR
not all items in E2:E500 = "5098", you get COUNT(FALSE) ... which for
reasons which I do not know, resolves to 1.

Why the latter puzzles me is because I know that counting an
array of numericals & logicals, whether entered as a regular or an array
formula will result in a count of only the numbers

yet this function count(FALSE) resolves to a value of 1 !!!


If you try it with SUM() instead of COUNT() you will get zero