Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Help
I apologize for my 3rd question w/in two days, but every time one thing
works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? I have similar questions that were answered using SUMPRODUCT: =SUMPRODUCT((V4:X100=2)*(L4:L100=1))/COUNTIF(V4:X100, "2") (a different question finding OF the students who identified as 'entrepreneur [2]' for program, also said they 'disagreed [1]' w/ a statement) but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") Is it because I'm looking at multiple columns for both values? I've entered in this equation ... and it is bringing up the wrong percentage. When doing the math manually (3 out of the 7 total entrepreneur students surveyed said B 111 was beneficial) -- the answer should be 43% ... but using this equation I keep coming up w/ 29%. Is there a different equation to use in this instance when there are multiple column ranges for both values? Thanks! Amber |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Help
"alh06" wrote:
I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. So it is best to post your messages as follow-ups to the original thread (pick one). That way, people who want to help you get the benefit of seeing the context, especially other responses. Moving forward, I suggest that you keep all related discussion in __this__ thread, since this seems to have the more complete description of the problem. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. [....] but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") [....] the answer should be 43% ... but using this equation I keep coming up w/ 29%. That formula counts the number of corresponding pairs of cells in V4:X100 and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and Q4, plus X4 and R4 . In other words, it is equivalent to: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111") + (W4:W100=2)*(Q4:Q100="B 111") + (X4:X100=2)*(R4:R100="B 111")) Apparently that is not how you want to count things. But it is unclear how you do want to count things. What if there are two or three 2s in V4, W4 and X4 -- that is, in the same row? Or is that not possible? What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in the same row? Or is that not possible? Assuming you want to count one or more 2s in one row and one or more "B 111"s in the same row as one, try: =SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)0) * ((P4:P100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")0) ) Note: That formulation seems necessary even if you expect only one 2 and one "B 111" in the same row, but not necessarily in the same relative column of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R. Please confirm (or refute) my assumption -- ``you want to count one or more 2s in one row and one or more "B 111"s in the same row as one``. Someone else might come up with a more terse way of expressing that formula. But that assumption seems to contradict the assumption of your divisor, unless you assume (and ideally ensure) that there is no more than one 2 and one "B 111" in the same row. If the latter, then you can continue to divide by COUNTIF(V4:X100,2) to compute percentage. ----- original message ----- "alh06" wrote in message ... I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? I have similar questions that were answered using SUMPRODUCT: =SUMPRODUCT((V4:X100=2)*(L4:L100=1))/COUNTIF(V4:X100, "2") (a different question finding OF the students who identified as 'entrepreneur [2]' for program, also said they 'disagreed [1]' w/ a statement) but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") Is it because I'm looking at multiple columns for both values? I've entered in this equation ... and it is bringing up the wrong percentage. When doing the math manually (3 out of the 7 total entrepreneur students surveyed said B 111 was beneficial) -- the answer should be 43% ... but using this equation I keep coming up w/ 29%. Is there a different equation to use in this instance when there are multiple column ranges for both values? Thanks! Amber |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Help
Thanks for responding. To clarify for you,
It IS impossible for there to be more than one 2 in a row (they can only choose each program once, so they can choose 3 different programs, but not the same program twice. One row could say V4= 3, W4 = 6, X4= 2 (or other combinations). So, it IS impossible for there to be more than one "B 111" in a row as well. Students can choose up to 3 different courses that were beneficial, so each course can only be in a row once. When using the equation you gave me, the answer still comes out wrong: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+ (X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking at V w/ P, W w/ Q, and W w/ R when what I need is for the equation to look at V through X at the same time, find a '2' and then see what rows also said "B 111" in columns P through R. Does that make sense? There can never be more than one 2 in a row, and there can never be more than one "B 111" in a row -- BUT a row can have both a 2 and a B 111 ... and THAT is what I'm looking for. How many rows have both. Hopefully that helps you in trying to figure out what equation I'm needing to use!!! "Joe User" wrote: "alh06" wrote: I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. So it is best to post your messages as follow-ups to the original thread (pick one). That way, people who want to help you get the benefit of seeing the context, especially other responses. Moving forward, I suggest that you keep all related discussion in __this__ thread, since this seems to have the more complete description of the problem. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. [....] but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") [....] the answer should be 43% ... but using this equation I keep coming up w/ 29%. That formula counts the number of corresponding pairs of cells in V4:X100 and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and Q4, plus X4 and R4 . In other words, it is equivalent to: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111") + (W4:W100=2)*(Q4:Q100="B 111") + (X4:X100=2)*(R4:R100="B 111")) Apparently that is not how you want to count things. But it is unclear how you do want to count things. What if there are two or three 2s in V4, W4 and X4 -- that is, in the same row? Or is that not possible? What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in the same row? Or is that not possible? Assuming you want to count one or more 2s in one row and one or more "B 111"s in the same row as one, try: =SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)0) * ((P4:P100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")0) ) Note: That formulation seems necessary even if you expect only one 2 and one "B 111" in the same row, but not necessarily in the same relative column of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R. Please confirm (or refute) my assumption -- ``you want to count one or more 2s in one row and one or more "B 111"s in the same row as one``. Someone else might come up with a more terse way of expressing that formula. But that assumption seems to contradict the assumption of your divisor, unless you assume (and ideally ensure) that there is no more than one 2 and one "B 111" in the same row. If the latter, then you can continue to divide by COUNTIF(V4:X100,2) to compute percentage. ----- original message ----- "alh06" wrote in message ... I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? I have similar questions that were answered using SUMPRODUCT: =SUMPRODUCT((V4:X100=2)*(L4:L100=1))/COUNTIF(V4:X100, "2") (a different question finding OF the students who identified as 'entrepreneur [2]' for program, also said they 'disagreed [1]' w/ a statement) but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") Is it because I'm looking at multiple columns for both values? I've entered in this equation ... and it is bringing up the wrong percentage. When doing the math manually (3 out of the 7 total entrepreneur students surveyed said B 111 was beneficial) -- the answer should be 43% ... but using this equation I keep coming up w/ 29%. Is there a different equation to use in this instance when there are multiple column ranges for both values? Thanks! Amber . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Help
"alh06" wrote in message ... Thanks for responding. To clarify for you, It IS impossible for there to be more than one 2 in a row (they can only choose each program once, so they can choose 3 different programs, but not the same program twice. One row could say V4= 3, W4 = 6, X4= 2 (or other combinations). So, it IS impossible for there to be more than one "B 111" in a row as well. Students can choose up to 3 different courses that were beneficial, so each course can only be in a row once. When using the equation you gave me, the answer still comes out wrong: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+ (X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking at V w/ P, W w/ Q, and W w/ R when what I need is for the equation to look at V through X at the same time, find a '2' and then see what rows also said "B 111" in columns P through R. Does that make sense? There can never be more than one 2 in a row, and there can never be more than one "B 111" in a row -- BUT a row can have both a 2 and a B 111 ... and THAT is what I'm looking for. How many rows have both. Hopefully that helps you in trying to figure out what equation I'm needing to use!!! "Joe User" wrote: "alh06" wrote: I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. So it is best to post your messages as follow-ups to the original thread (pick one). That way, people who want to help you get the benefit of seeing the context, especially other responses. Moving forward, I suggest that you keep all related discussion in __this__ thread, since this seems to have the more complete description of the problem. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. [....] but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") [....] the answer should be 43% ... but using this equation I keep coming up w/ 29%. That formula counts the number of corresponding pairs of cells in V4:X100 and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and Q4, plus X4 and R4 . In other words, it is equivalent to: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111") + (W4:W100=2)*(Q4:Q100="B 111") + (X4:X100=2)*(R4:R100="B 111")) Apparently that is not how you want to count things. But it is unclear how you do want to count things. What if there are two or three 2s in V4, W4 and X4 -- that is, in the same row? Or is that not possible? What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in the same row? Or is that not possible? Assuming you want to count one or more 2s in one row and one or more "B 111"s in the same row as one, try: =SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)0) * ((P4:P100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")0) ) Note: That formulation seems necessary even if you expect only one 2 and one "B 111" in the same row, but not necessarily in the same relative column of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R. Please confirm (or refute) my assumption -- ``you want to count one or more 2s in one row and one or more "B 111"s in the same row as one``. Someone else might come up with a more terse way of expressing that formula. But that assumption seems to contradict the assumption of your divisor, unless you assume (and ideally ensure) that there is no more than one 2 and one "B 111" in the same row. If the latter, then you can continue to divide by COUNTIF(V4:X100,2) to compute percentage. ----- original message ----- "alh06" wrote in message ... I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? I have similar questions that were answered using SUMPRODUCT: =SUMPRODUCT((V4:X100=2)*(L4:L100=1))/COUNTIF(V4:X100, "2") (a different question finding OF the students who identified as 'entrepreneur [2]' for program, also said they 'disagreed [1]' w/ a statement) but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") Is it because I'm looking at multiple columns for both values? I've entered in this equation ... and it is bringing up the wrong percentage. When doing the math manually (3 out of the 7 total entrepreneur students surveyed said B 111 was beneficial) -- the answer should be 43% ... but using this equation I keep coming up w/ 29%. Is there a different equation to use in this instance when there are multiple column ranges for both values? Thanks! Amber . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Help
"alh06" wrote:
When using the equation you gave me, the answer still comes out wrong: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+ (X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking at V w/ P, W w/ Q, and W w/ R You misread my response. That is __not__ the formula I was proposing. The formula above was intended to demonstrate the flaw in the formula that you were trying to leverage, namely =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111")). And yes, the flaw is exactly as you (and I) described it. The alternative formula that I proposed is: =SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)0) * ((P4:P100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")0) ) As I explained previously (emphasis added): "That formulation seems necessary __even _if__ you expect only one 2 and one "B 111" in the same row, but not necessarily in the same relative column of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R. Please let me know if that resolves your problem. [Sorry for the previous contentless response. I pushed the wrong button yet again <sigh.] ----- original message ----- "alh06" wrote in message ... Thanks for responding. To clarify for you, It IS impossible for there to be more than one 2 in a row (they can only choose each program once, so they can choose 3 different programs, but not the same program twice. One row could say V4= 3, W4 = 6, X4= 2 (or other combinations). So, it IS impossible for there to be more than one "B 111" in a row as well. Students can choose up to 3 different courses that were beneficial, so each course can only be in a row once. When using the equation you gave me, the answer still comes out wrong: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+ (X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking at V w/ P, W w/ Q, and W w/ R when what I need is for the equation to look at V through X at the same time, find a '2' and then see what rows also said "B 111" in columns P through R. Does that make sense? There can never be more than one 2 in a row, and there can never be more than one "B 111" in a row -- BUT a row can have both a 2 and a B 111 ... and THAT is what I'm looking for. How many rows have both. Hopefully that helps you in trying to figure out what equation I'm needing to use!!! "Joe User" wrote: "alh06" wrote: I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. So it is best to post your messages as follow-ups to the original thread (pick one). That way, people who want to help you get the benefit of seeing the context, especially other responses. Moving forward, I suggest that you keep all related discussion in __this__ thread, since this seems to have the more complete description of the problem. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. [....] but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") [....] the answer should be 43% ... but using this equation I keep coming up w/ 29%. That formula counts the number of corresponding pairs of cells in V4:X100 and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and Q4, plus X4 and R4 . In other words, it is equivalent to: =SUMPRODUCT((V4:X100=2)*(P4:P100="B 111") + (W4:W100=2)*(Q4:Q100="B 111") + (X4:X100=2)*(R4:R100="B 111")) Apparently that is not how you want to count things. But it is unclear how you do want to count things. What if there are two or three 2s in V4, W4 and X4 -- that is, in the same row? Or is that not possible? What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in the same row? Or is that not possible? Assuming you want to count one or more 2s in one row and one or more "B 111"s in the same row as one, try: =SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)0) * ((P4:P100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")0) ) Note: That formulation seems necessary even if you expect only one 2 and one "B 111" in the same row, but not necessarily in the same relative column of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R. Please confirm (or refute) my assumption -- ``you want to count one or more 2s in one row and one or more "B 111"s in the same row as one``. Someone else might come up with a more terse way of expressing that formula. But that assumption seems to contradict the assumption of your divisor, unless you assume (and ideally ensure) that there is no more than one 2 and one "B 111" in the same row. If the latter, then you can continue to divide by COUNTIF(V4:X100,2) to compute percentage. ----- original message ----- "alh06" wrote in message ... I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? I have similar questions that were answered using SUMPRODUCT: =SUMPRODUCT((V4:X100=2)*(L4:L100=1))/COUNTIF(V4:X100, "2") (a different question finding OF the students who identified as 'entrepreneur [2]' for program, also said they 'disagreed [1]' w/ a statement) but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") Is it because I'm looking at multiple columns for both values? I've entered in this equation ... and it is bringing up the wrong percentage. When doing the math manually (3 out of the 7 total entrepreneur students surveyed said B 111 was beneficial) -- the answer should be 43% ... but using this equation I keep coming up w/ 29%. Is there a different equation to use in this instance when there are multiple column ranges for both values? Thanks! Amber . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
More SUMPRODUCT help | Excel Discussion (Misc queries) | |||
SUMProduct help | Excel Discussion (Misc queries) | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions |