Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
I have a company fitness chart, with "X"s indicating participation on certain
days of the week. The requestor wants a sub-totaled percentage for fitness sessions on Tuesdays and Thursdays (combined) and for Wednesdays, plus an overall score. The overall participation percentage is no problem. For the sub-scores, I am using a COUNTIFS function: =COUNTIFS(--(B2:P2),"TUES",--(B2:P2),"THURS",--(B3:P3),"X"). But, this produces an error. (What do the paired dashes mean, anyway)? I have tried a couple of other functions, COUNTA, COUNTIF and IF, plus some of the SUMPRODUCT examples from the discussion page. They do not work as advertised either, although I thought I was close at one point. Suggestions are welcome, as always. DOUG |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
Try it like this:
=SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "DOUG" wrote in message ... I have a company fitness chart, with "X"s indicating participation on certain days of the week. The requestor wants a sub-totaled percentage for fitness sessions on Tuesdays and Thursdays (combined) and for Wednesdays, plus an overall score. The overall participation percentage is no problem. For the sub-scores, I am using a COUNTIFS function: =COUNTIFS(--(B2:P2),"TUES",--(B2:P2),"THURS",--(B3:P3),"X"). But, this produces an error. (What do the paired dashes mean, anyway)? I have tried a couple of other functions, COUNTA, COUNTIF and IF, plus some of the SUMPRODUCT examples from the discussion page. They do not work as advertised either, although I thought I was close at one point. Suggestions are welcome, as always. DOUG |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
T. Valko: That worked like a charm. Thanks.
PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "DOUG" wrote in message ... I have a company fitness chart, with "X"s indicating participation on certain days of the week. The requestor wants a sub-totaled percentage for fitness sessions on Tuesdays and Thursdays (combined) and for Wednesdays, plus an overall score. The overall participation percentage is no problem. For the sub-scores, I am using a COUNTIFS function: =COUNTIFS(--(B2:P2),"TUES",--(B2:P2),"THURS",--(B3:P3),"X"). But, this produces an error. (What do the paired dashes mean, anyway)? I have tried a couple of other functions, COUNTA, COUNTIF and IF, plus some of the SUMPRODUCT examples from the discussion page. They do not work as advertised either, although I thought I was close at one point. Suggestions are welcome, as always. DOUG |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
"PRODUCT" implies multiplication to me,
but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 Ok, now let's look at your formula and see why it didn't work: =COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X") COUNTIFS does "straight comparisons" only. Using the double unary, you're attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It has to be the straight comparison: =COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X") However, there's still a probem with that. Since the cells cannot hold both Tues and Thurs at the same time the result will always be 0. I'm not sure of the "internal workings" of the COUNTIFS function but it can be explained the same way as SUMPRODUCT is: array multiplication. B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE But in this case the array multiplication is done a bit differently: FALSE,TRUE,FALSE * FALSE,FALSE,TRUE * TRUE,TRUE,TRUE = ......0........0........0........ -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: That worked like a charm. Thanks. PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "DOUG" wrote in message ... I have a company fitness chart, with "X"s indicating participation on certain days of the week. The requestor wants a sub-totaled percentage for fitness sessions on Tuesdays and Thursdays (combined) and for Wednesdays, plus an overall score. The overall participation percentage is no problem. For the sub-scores, I am using a COUNTIFS function: =COUNTIFS(--(B2:P2),"TUES",--(B2:P2),"THURS",--(B3:P3),"X"). But, this produces an error. (What do the paired dashes mean, anyway)? I have tried a couple of other functions, COUNTA, COUNTIF and IF, plus some of the SUMPRODUCT examples from the discussion page. They do not work as advertised either, although I thought I was close at one point. Suggestions are welcome, as always. DOUG |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
T.Valko and or Biff: Thank you very much. I understand what your are
saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 Ok, now let's look at your formula and see why it didn't work: =COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X") COUNTIFS does "straight comparisons" only. Using the double unary, you're attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It has to be the straight comparison: =COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X") However, there's still a probem with that. Since the cells cannot hold both Tues and Thurs at the same time the result will always be 0. I'm not sure of the "internal workings" of the COUNTIFS function but it can be explained the same way as SUMPRODUCT is: array multiplication. B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE But in this case the array multiplication is done a bit differently: FALSE,TRUE,FALSE * FALSE,FALSE,TRUE * TRUE,TRUE,TRUE = ......0........0........0........ -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: That worked like a charm. Thanks. PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "DOUG" wrote in message ... I have a company fitness chart, with "X"s indicating participation on certain days of the week. The requestor wants a sub-totaled percentage for fitness sessions on Tuesdays and Thursdays (combined) and for Wednesdays, plus an overall score. The overall participation percentage is no problem. For the sub-scores, I am using a COUNTIFS function: =COUNTIFS(--(B2:P2),"TUES",--(B2:P2),"THURS",--(B3:P3),"X"). But, this produces an error. (What do the paired dashes mean, anyway)? I have tried a couple of other functions, COUNTA, COUNTIF and IF, plus some of the SUMPRODUCT examples from the discussion page. They do not work as advertised either, although I thought I was close at one point. Suggestions are welcome, as always. DOUG |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
the formula drives the product to a number and
then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 Ok, now let's look at your formula and see why it didn't work: =COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X") COUNTIFS does "straight comparisons" only. Using the double unary, you're attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It has to be the straight comparison: =COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X") However, there's still a probem with that. Since the cells cannot hold both Tues and Thurs at the same time the result will always be 0. I'm not sure of the "internal workings" of the COUNTIFS function but it can be explained the same way as SUMPRODUCT is: array multiplication. B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE But in this case the array multiplication is done a bit differently: FALSE,TRUE,FALSE * FALSE,FALSE,TRUE * TRUE,TRUE,TRUE = ......0........0........0........ -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: That worked like a charm. Thanks. PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- Biff Microsoft Excel MVP "DOUG" wrote in message ... I have a company fitness chart, with "X"s indicating participation on certain days of the week. The requestor wants a sub-totaled percentage for fitness sessions on Tuesdays and Thursdays (combined) and for Wednesdays, plus an overall score. The overall participation percentage is no problem. For the sub-scores, I am using a COUNTIFS function: =COUNTIFS(--(B2:P2),"TUES",--(B2:P2),"THURS",--(B3:P3),"X"). But, this produces an error. (What do the paired dashes mean, anyway)? I have tried a couple of other functions, COUNTA, COUNTIF and IF, plus some of the SUMPRODUCT examples from the discussion page. They do not work as advertised either, although I thought I was close at one point. Suggestions are welcome, as always. DOUG |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
T.Valko, aka "Biff": Thank you very much. I believe I have learned a lot
today. Q: Is there a dummed down reference page to tell me how the symbols come together to form expressions? I have "VBA for Dummies", but even that is somewhat over my head. I am just looking for a couple of pages of coding guidance. Sincerely, DOUG ECKERT "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 Ok, now let's look at your formula and see why it didn't work: =COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X") COUNTIFS does "straight comparisons" only. Using the double unary, you're attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It has to be the straight comparison: =COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X") However, there's still a probem with that. Since the cells cannot hold both Tues and Thurs at the same time the result will always be 0. I'm not sure of the "internal workings" of the COUNTIFS function but it can be explained the same way as SUMPRODUCT is: array multiplication. B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE But in this case the array multiplication is done a bit differently: FALSE,TRUE,FALSE * FALSE,FALSE,TRUE * TRUE,TRUE,TRUE = ......0........0........0........ -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: That worked like a charm. Thanks. PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the
range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 Ok, now let's look at your formula and see why it didn't work: =COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X") COUNTIFS does "straight comparisons" only. Using the double unary, you're attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It has to be the straight comparison: =COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X") However, there's still a probem with that. Since the cells cannot hold both Tues and Thurs at the same time the result will always be 0. I'm not sure of the "internal workings" of the COUNTIFS function but it can be explained the same way as SUMPRODUCT is: array multiplication. B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE But in this case the array multiplication is done a bit differently: FALSE,TRUE,FALSE * FALSE,FALSE,TRUE * TRUE,TRUE,TRUE = ......0........0........0........ -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: That worked like a charm. Thanks. PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
That's kind of a hard question to answer!
Here's a good list of Excel related books: http://contextures.com/xlbooks.html You can also learn a lot right on this forum. -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko, aka "Biff": Thank you very much. I believe I have learned a lot today. Q: Is there a dummed down reference page to tell me how the symbols come together to form expressions? I have "VBA for Dummies", but even that is somewhat over my head. I am just looking for a couple of pages of coding guidance. Sincerely, DOUG ECKERT "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 Ok, now let's look at your formula and see why it didn't work: =COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X") COUNTIFS does "straight comparisons" only. Using the double unary, you're attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It has to be the straight comparison: =COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X") However, there's still a probem with that. Since the cells cannot hold both Tues and Thurs at the same time the result will always be 0. I'm not sure of the "internal workings" of the COUNTIFS function but it can be explained the same way as SUMPRODUCT is: array multiplication. B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE But in this case the array multiplication is done a bit differently: FALSE,TRUE,FALSE * FALSE,FALSE,TRUE * TRUE,TRUE,TRUE = ......0........0........0........ -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: That worked like a charm. Thanks. PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
Is (F2<F$2:F$4) saying...
It's comparing F2 against every cell in the range F2:F4. F2 = 110 F3 = 110 F4 = 100 Is F2 (110) less than F2 (110) = no = FALSE Is F2 (110) less than F3 (110) = no = FALSE Is F2 (110) less than F4 (100) = no = FALSE As you drag copy the formula down the column the reference to F2 will increment so that you get: Is F3 (110) less than F2 (110) = no = FALSE Is F3 (110) less than F3 (110) = no = FALSE Is F3 (110) less than F4 (100) = no = FALSE Is F4 (100) less than F2 (110) = yes = TRUE Is F4 (100) less than F3 (110) = yes = TRUE Is F4 (100) less than F4 (100) = no = FALSE COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? Not exactly! A1:A10 = numbers B1:B10 = whatever Count where A = 5 *or* A = 10 *and* B = x =SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x")) =SUMPRODUCT((A1:A10=5)+(A1:A10=10),--(B1:B10="x")) =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x")) All 3 of those formulas will do that. So, just looking at those formulas which one would think is the best formula to use for this? If you said the 3rd one you'd be right! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 Ok, now let's look at your formula and see why it didn't work: =COUNTIFS(--(B2:D2),"TUES",--(B2:D2),"THURS",--(B3:D3),"X") COUNTIFS does "straight comparisons" only. Using the double unary, you're attempting to "manipulate" the arrays and you can't do that in COUNTIFS. It has to be the straight comparison: =COUNTIFS(B2:D2,"TUES",B2:D2,"THURS",B3:D3,"X") However, there's still a probem with that. Since the cells cannot hold both Tues and Thurs at the same time the result will always be 0. I'm not sure of the "internal workings" of the COUNTIFS function but it can be explained the same way as SUMPRODUCT is: array multiplication. B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE But in this case the array multiplication is done a bit differently: FALSE,TRUE,FALSE * FALSE,FALSE,TRUE * TRUE,TRUE,TRUE = ......0........0........0........ -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: That worked like a charm. Thanks. PS, Could you please explain it to me. I looked up the SUMPRODUCT function earlier today, based on your examples. "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Also, when I tried COUNTIFS earlier, it worked in one cell and not in another. The version of it I showed above should have worked as far as I know. Would you care to comment? DOUG "T. Valko" wrote: Try it like this: =SUMPRODUCT((B2:P2={"Tues";"Thurs"})*(B3:P3="x")) What do the paired dashes mean, anyway They can't be used in the COUNTIFS like you have them. See this: http://mcgimpsey.com/excel/formulae/doubleneg.html -- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
WOW!
Thanks again, T. Valko (BIFF). DOUG "T. Valko" wrote: Is (F2<F$2:F$4) saying... It's comparing F2 against every cell in the range F2:F4. F2 = 110 F3 = 110 F4 = 100 Is F2 (110) less than F2 (110) = no = FALSE Is F2 (110) less than F3 (110) = no = FALSE Is F2 (110) less than F4 (100) = no = FALSE As you drag copy the formula down the column the reference to F2 will increment so that you get: Is F3 (110) less than F2 (110) = no = FALSE Is F3 (110) less than F3 (110) = no = FALSE Is F3 (110) less than F4 (100) = no = FALSE Is F4 (100) less than F2 (110) = yes = TRUE Is F4 (100) less than F3 (110) = yes = TRUE Is F4 (100) less than F4 (100) = no = FALSE COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? Not exactly! A1:A10 = numbers B1:B10 = whatever Count where A = 5 *or* A = 10 *and* B = x =SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x")) =SUMPRODUCT((A1:A10=5)+(A1:A10=10),--(B1:B10="x")) =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x")) All 3 of those formulas will do that. So, just looking at those formulas which one would think is the best formula to use for this? If you said the 3rd one you'd be right! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
You're welcome!
-- Biff Microsoft Excel MVP "DOUG" wrote in message ... WOW! Thanks again, T. Valko (BIFF). DOUG "T. Valko" wrote: Is (F2<F$2:F$4) saying... It's comparing F2 against every cell in the range F2:F4. F2 = 110 F3 = 110 F4 = 100 Is F2 (110) less than F2 (110) = no = FALSE Is F2 (110) less than F3 (110) = no = FALSE Is F2 (110) less than F4 (100) = no = FALSE As you drag copy the formula down the column the reference to F2 will increment so that you get: Is F3 (110) less than F2 (110) = no = FALSE Is F3 (110) less than F3 (110) = no = FALSE Is F3 (110) less than F4 (100) = no = FALSE Is F4 (100) less than F2 (110) = yes = TRUE Is F4 (100) less than F3 (110) = yes = TRUE Is F4 (100) less than F4 (100) = no = FALSE COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? Not exactly! A1:A10 = numbers B1:B10 = whatever Count where A = 5 *or* A = 10 *and* B = x =SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x")) =SUMPRODUCT((A1:A10=5)+(A1:A10=10),--(B1:B10="x")) =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x")) All 3 of those formulas will do that. So, just looking at those formulas which one would think is the best formula to use for this? If you said the 3rd one you'd be right! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. TRUE * TRUE = 1 TRUE * FALSE = 0 FALSE * TRUE = 0 FALSE * FALSE = 0 So, here's how those arrays are multiplied together and the result: B2:D2 = Tues = FALSE,TRUE,FALSE * B3:D3 = x = TRUE,TRUE,TRUE = {0,1,0} B2:D2 = Thurs = FALSE,FALSE,TRUE * B3:D3 = x = TRUE,TRUE,TRUE = {0,0,1} Now, when the formula calculates this produces one array like this: {0,1,0;0,0,1} So, if PRODUCT means to multiply then SUMPRODUCT means the SUM of PRODUCTS. The PRODUCTS are {0,1,0;0,0,1} The SUM of those PRODUCTS are 0+1+0+0+0+1 So: SUMPRODUCT({0,1,0;0,0,1}) = 2 =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
T. Valko: One more twist. The requestor wants to set the denminator in the
TUE-THU column to count only Tuesdays and Thursdays instead of counting all days. I have played with it to no avail, since I do not speak this language yet. I did try plugging in the COUNTIFS function in the denominator and I did try plugging in part of the numerator from your SUMPRODUCT equation into the denominator, but 'no luck. 'Any suggestions? DOUG "T. Valko" wrote: You're welcome! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... WOW! Thanks again, T. Valko (BIFF). DOUG "T. Valko" wrote: Is (F2<F$2:F$4) saying... It's comparing F2 against every cell in the range F2:F4. F2 = 110 F3 = 110 F4 = 100 Is F2 (110) less than F2 (110) = no = FALSE Is F2 (110) less than F3 (110) = no = FALSE Is F2 (110) less than F4 (100) = no = FALSE As you drag copy the formula down the column the reference to F2 will increment so that you get: Is F3 (110) less than F2 (110) = no = FALSE Is F3 (110) less than F3 (110) = no = FALSE Is F3 (110) less than F4 (100) = no = FALSE Is F4 (100) less than F2 (110) = yes = TRUE Is F4 (100) less than F3 (110) = yes = TRUE Is F4 (100) less than F4 (100) = no = FALSE COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? Not exactly! A1:A10 = numbers B1:B10 = whatever Count where A = 5 *or* A = 10 *and* B = x =SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x")) =SUMPRODUCT((A1:A10=5)+(A1:A10=10),--(B1:B10="x")) =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x")) All 3 of those formulas will do that. So, just looking at those formulas which one would think is the best formula to use for this? If you said the 3rd one you'd be right! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
I'm not following you!
You'll have to explain the whole problem. -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: One more twist. The requestor wants to set the denminator in the TUE-THU column to count only Tuesdays and Thursdays instead of counting all days. I have played with it to no avail, since I do not speak this language yet. I did try plugging in the COUNTIFS function in the denominator and I did try plugging in part of the numerator from your SUMPRODUCT equation into the denominator, but 'no luck. 'Any suggestions? DOUG "T. Valko" wrote: You're welcome! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... WOW! Thanks again, T. Valko (BIFF). DOUG "T. Valko" wrote: Is (F2<F$2:F$4) saying... It's comparing F2 against every cell in the range F2:F4. F2 = 110 F3 = 110 F4 = 100 Is F2 (110) less than F2 (110) = no = FALSE Is F2 (110) less than F3 (110) = no = FALSE Is F2 (110) less than F4 (100) = no = FALSE As you drag copy the formula down the column the reference to F2 will increment so that you get: Is F3 (110) less than F2 (110) = no = FALSE Is F3 (110) less than F3 (110) = no = FALSE Is F3 (110) less than F4 (100) = no = FALSE Is F4 (100) less than F2 (110) = yes = TRUE Is F4 (100) less than F3 (110) = yes = TRUE Is F4 (100) less than F4 (100) = no = FALSE COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? Not exactly! A1:A10 = numbers B1:B10 = whatever Count where A = 5 *or* A = 10 *and* B = x =SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x")) =SUMPRODUCT((A1:A10=5)+(A1:A10=10),--(B1:B10="x")) =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x")) All 3 of those formulas will do that. So, just looking at those formulas which one would think is the best formula to use for this? If you said the 3rd one you'd be right! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what the expression means. (I think this came from Biff Biffenden or Gordon Dibben originally). Thanks a lot, DOUG ECKERT "T. Valko" wrote: "PRODUCT" implies multiplication to me, but that does not appear to be the case here. Yes, that's what's happening. Here's how it works... Let's use a small data sample: ...........B..........C..........D 2......Mon.....Tues.....Thurs 3.......x...........x...........x =SUMPRODUCT((B2:D2={"Tues";"Thurs"})*(B3:D3="x")) =2 These expressions will return arrays of either TRUE or FALSE: (B2:D2={"Tues";"Thurs"}) (B3:D3="x") It would look like this: B2:D2 = Tues = FALSE,TRUE,FALSE B2:D2 = Thurs = FALSE,FALSE,TRUE B3:D3 = x = TRUE,TRUE,TRUE These arrays are then multiplied together: (B2:D2={"Tues";"Thurs"})*(B3:D3="x") Performing any math operation on a logical value (TRUE, FALSE) will coerce the result to a numeric value. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
T. Valko: The problem is that (Tue-Thur) shows the percent of ALL of the
days of the month instead of the percent of just the Tue and Thur when an individual worked out. So, the denominator consists of all days instead of just the Tue and Thur. =(SUMPRODUCT(($B$2:$P$2={"Tues";"Thurs"})*(B3:P3=" x"))/(ROWS(B3:P3)*COLUMNS($B$3:$P$3))) DOUG "T. Valko" wrote: I'm not following you! You'll have to explain the whole problem. -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: One more twist. The requestor wants to set the denminator in the TUE-THU column to count only Tuesdays and Thursdays instead of counting all days. I have played with it to no avail, since I do not speak this language yet. I did try plugging in the COUNTIFS function in the denominator and I did try plugging in part of the numerator from your SUMPRODUCT equation into the denominator, but 'no luck. 'Any suggestions? DOUG "T. Valko" wrote: You're welcome! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... WOW! Thanks again, T. Valko (BIFF). DOUG "T. Valko" wrote: Is (F2<F$2:F$4) saying... It's comparing F2 against every cell in the range F2:F4. F2 = 110 F3 = 110 F4 = 100 Is F2 (110) less than F2 (110) = no = FALSE Is F2 (110) less than F3 (110) = no = FALSE Is F2 (110) less than F4 (100) = no = FALSE As you drag copy the formula down the column the reference to F2 will increment so that you get: Is F3 (110) less than F2 (110) = no = FALSE Is F3 (110) less than F3 (110) = no = FALSE Is F3 (110) less than F4 (100) = no = FALSE Is F4 (100) less than F2 (110) = yes = TRUE Is F4 (100) less than F3 (110) = yes = TRUE Is F4 (100) less than F4 (100) = no = FALSE COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? Not exactly! A1:A10 = numbers B1:B10 = whatever Count where A = 5 *or* A = 10 *and* B = x =SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x")) =SUMPRODUCT((A1:A10=5)+(A1:A10=10),--(B1:B10="x")) =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x")) All 3 of those formulas will do that. So, just looking at those formulas which one would think is the best formula to use for this? If you said the 3rd one you'd be right! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIFs Based on Field Headings
If you want the percentage of "Tues and Thurs = x" out of all x's:
=SUMPRODUCT(($B$2:$P$2={"Tues";"Thurs"})*(B3:P3="x "))/COUNTIF(B3:P3,"x") -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: The problem is that (Tue-Thur) shows the percent of ALL of the days of the month instead of the percent of just the Tue and Thur when an individual worked out. So, the denominator consists of all days instead of just the Tue and Thur. =(SUMPRODUCT(($B$2:$P$2={"Tues";"Thurs"})*(B3:P3=" x"))/(ROWS(B3:P3)*COLUMNS($B$3:$P$3))) DOUG "T. Valko" wrote: I'm not following you! You'll have to explain the whole problem. -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: One more twist. The requestor wants to set the denminator in the TUE-THU column to count only Tuesdays and Thursdays instead of counting all days. I have played with it to no avail, since I do not speak this language yet. I did try plugging in the COUNTIFS function in the denominator and I did try plugging in part of the numerator from your SUMPRODUCT equation into the denominator, but 'no luck. 'Any suggestions? DOUG "T. Valko" wrote: You're welcome! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... WOW! Thanks again, T. Valko (BIFF). DOUG "T. Valko" wrote: Is (F2<F$2:F$4) saying... It's comparing F2 against every cell in the range F2:F4. F2 = 110 F3 = 110 F4 = 100 Is F2 (110) less than F2 (110) = no = FALSE Is F2 (110) less than F3 (110) = no = FALSE Is F2 (110) less than F4 (100) = no = FALSE As you drag copy the formula down the column the reference to F2 will increment so that you get: Is F3 (110) less than F2 (110) = no = FALSE Is F3 (110) less than F3 (110) = no = FALSE Is F3 (110) less than F4 (100) = no = FALSE Is F4 (100) less than F2 (110) = yes = TRUE Is F4 (100) less than F3 (110) = yes = TRUE Is F4 (100) less than F4 (100) = no = FALSE COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? Not exactly! A1:A10 = numbers B1:B10 = whatever Count where A = 5 *or* A = 10 *and* B = x =SUM(COUNTIFS(A1:A10,{5,10},B1:B10,"x")) =SUMPRODUCT((A1:A10=5)+(A1:A10=10),--(B1:B10="x")) =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{5,10}))),--(B1:B10="x")) All 3 of those formulas will do that. So, just looking at those formulas which one would think is the best formula to use for this? If you said the 3rd one you'd be right! -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T. Valko: Is (F2<F$2:F$4) saying "where F2 is less than the _______ of the range F$2:F$4)? Also, in the COUNTIFS function, is there a way to change the AND interpretation to an OR interpretation? DOUG "T. Valko" wrote: the formula drives the product to a number and then sums the number, yes? Yes the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. No, the COUNTIFS works on the logic of *AND*. That's why it didn't work. B2 = Tues B3 = x =COUNTIFS(B2,"TUES",B2,"THURS",B3,"X") Count if B2 = Tues and B2 = Thurs and B3 = x B2 can't be both Tues and Thurs at the same time so: TRUE * FALSE * TRUE = 0 An example of using "AND" logic on the same range is when testing numbers (or dates/times) to be within a range. For example: B2 = 10 B3 = x Count if B2 =0 and B2 <=25 and B3 = x B2 can be both =0 and <=25 at the same time. =COUNTIFS(B2,"=0",B2,"<=25",B3,"x") TRUE * TRUE * TRUE = 1 Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 That formula produces counts (ranks) from highest to lowest and does it so that the counts (ranks) are consecutive. F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 2 Using the RANK function the results would be: F2 = 110 = 1 F3 = 110 = 1 F4 = 100 = 3 =SUMPRODUCT(--(F2<F$2:F$4),1/COUNTIF(F$2:F$4,F$2:F$4))+1 This expression will return an array of either TRUE or FALSE: (F2<F$2:F$4) 110 < 110 = F 110 < 110 = F 110 < 100 = F Because SUMPRODUCT sums the products we need to convert those T and F to numbers. One to do that is through a math operation like we did in the other SUMPRODUCT formula. In this example we're using the double unary which will do the same thing: --TRUE = 1 --FALSE = 0 So this array: --(F2<F$2:F$4) = {0;0;0} SUMPRODUCT is based on array multiplication. We have the first array: {0;0;0}, now we need another array to get our result. We get the second array from this expression: 1/COUNTIF(F$2:F$4,F$2:F$4) This is also the expression that allows for duplicate numbers to be ranked the same. First we get a series of COUNTIFs: COUNTIF(F$2:F$4,F$2) = 2 COUNTIF(F$2:F$4,F$3) = 2 COUNTIF(F$2:F$4,F$4) = 1 Then we divide these counts by 1. This is what allows the formula to rank duplicate numbers the same. 1/2 = 0.5 1/2 = 0.5 1/1 = 1 Now we have the second array: {0.5;0.5;1} =SUMPRODUCT({0;0;0},{0.5;0.5;1}) {0;0;0}*{0.5;0.5;1} = 0 Now, you might be thinking that a result of 0 doesn't make any sense. Since this example has been evaluating cell F2 which is the highest number in the range we know that none of the numbers to be ranked will meet this condition: (F2<F$2:F$4) F2 (110) is not less than any number to be ranked. So the first array was all 0s: --(F2<F$2:F$4) = {0;0;0}. And we know that an array of all 0s will return 0 as the result of the SUMPRODUCT function. But, that's why we add 1 at the very end of the formula: =SUMPRODUCT(0)+1 So, F2 (110) is ranked 1. As the formula is copied down each number in the range gets evalauated like this and we end up with the final ranks of: 110 = 1 110 = 1 100 = 2 -- Biff Microsoft Excel MVP "DOUG" wrote in message ... T.Valko and or Biff: Thank you very much. I understand what your are saying, but not all of the terminology. Paraphrasing, the formula drives the product to a number and then sums the number, yes? Also, the COUNTIFS did not work because it was interpreting criteria to mean "OR" rather than "AND" and it appears to be hardwired that way - although, you would think specifying multiple criteria would default to "AND", as in, "if this is true AND this is TRUE" and so on. Aside: Speaking of the SUMPRODUCT function, please interpret this expression... =SUMPRODUCT(--(F2<F$2:F$79),1/COUNTIF(F$2:F$79,F$2:F$79))+1 I use it to rank scores all the time and it is great, but I do not know what |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countifs of prior years monthly data based on MTD today | Excel Worksheet Functions | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
calculate a field based on value of another field | Excel Discussion (Misc queries) | |||
calculated field based on data field | Excel Discussion (Misc queries) | |||
Look up based on Column and Row headings | Excel Worksheet Functions |