Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
In the formula below, what does the asterisk '*' mean?
=COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
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 "Andrew Chalk" wrote in message ... In the formula below, what does the asterisk '*' mean? =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
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")*(D5:D7))} 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 "Andrew Chalk" wrote in message ... In the formula below, what does the asterisk '*' mean? =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
"Andrew Chalk" wrote...
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. |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
OK. A cross-product in matrix parlance?
Thanks for the detail. Same goes for QWilliam and Harlan's replies. - Andrew "Chip Pearson" wrote in message ... 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 "Andrew Chalk" wrote in message ... In the formula below, what does the asterisk '*' mean? =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
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. "Andrew Chalk" wrote in message ... OK. A cross-product in matrix parlance? Thanks for the detail. Same goes for QWilliam and Harlan's replies. - Andrew "Chip Pearson" wrote in message ... 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 "Andrew Chalk" wrote in message ... In the formula below, what does the asterisk '*' mean? =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks |
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
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 "Chip Pearson" wrote in message ... 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 "Andrew Chalk" wrote in message ... In the formula below, what does the asterisk '*' mean? =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks |
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
What does this mean?
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 "Bill Ridgeway" wrote in message ... 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 "Chip Pearson" wrote in message ... 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 "Andrew Chalk" wrote in message ... In the formula below, what does the asterisk '*' mean? =COUNT(IF((A2:A500="5/27/2005")*(E2:E500="5098"),E2:E500)) Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|