Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ Formula!
This is what I am trying to find:
Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- I have no clue what formula to use - I tried using COUNTIF combined with IF and thought it was right, but now certain numbers aren't working out correctly. Then i thought maybe I needed to use AND as I'm trying to find two things?? If anyone can give me a template using my information above, that would be fantastic! Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ Formula!
=sumproduct(--(a2:a99=2),--(b2:b99=5))
will give the number of rows that have 2's in column A and at the same time 5's in column B. Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ============ If you're using xl2007, there's a new =countifs() function that will do the same kind of thing. ======= To determine the percentage, you can divide this total by the overall total (I think that's what you want). =Counta(a2:a99) will return the number on non-empty cells in that range. So maybe... =sumproduct(--(a2:a99=2),--(b2:b99=5)) / counta(a2:a99) is what you want (and format as a percentage). alh06 wrote: This is what I am trying to find: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- I have no clue what formula to use - I tried using COUNTIF combined with IF and thought it was right, but now certain numbers aren't working out correctly. Then i thought maybe I needed to use AND as I'm trying to find two things?? If anyone can give me a template using my information above, that would be fantastic! Thank you! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ Formula!
Hello,
=SUMPRODUCT(--(A2:A9=2),--(B2:B9=5))/COUNT(A2:A9) will return the percentage of the total number in col a with values of 2 who also have the value 5 on the corresponding row of col b. Is that what you're after? -- Cheers, MarkN "alh06" wrote: This is what I am trying to find: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- I have no clue what formula to use - I tried using COUNTIF combined with IF and thought it was right, but now certain numbers aren't working out correctly. Then i thought maybe I needed to use AND as I'm trying to find two things?? If anyone can give me a template using my information above, that would be fantastic! Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ Formula!
Yes, that IS what I'm looking for!! I had to change the COUNT part to COUNTIF
because I'm only looking to find the percentage of the number of "2"s that answered, but I could change that on my own. :) Now I have another question ... when I'm looking at people who identified as a '2' -- I'm actually looking at 3 columns for those numbers (range 4:100). How do I put that into the equation ... I tried to just write =SUMPRODUCT(--(V4:W100=2),--(E4:E100=5)) but it said the Value was wrong ... so I tried to put V4:V100, W4:W100, X4:X100 and that didn't work either. I'm guessing I have to put them each in on their own, but I don't know entirely where they go in regard to parenthesis and funny dashes. :) Thank you so much!! Amber "MarkN" wrote: Hello, =SUMPRODUCT(--(A2:A9=2),--(B2:B9=5))/COUNT(A2:A9) will return the percentage of the total number in col a with values of 2 who also have the value 5 on the corresponding row of col b. Is that what you're after? -- Cheers, MarkN "alh06" wrote: This is what I am trying to find: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- I have no clue what formula to use - I tried using COUNTIF combined with IF and thought it was right, but now certain numbers aren't working out correctly. Then i thought maybe I needed to use AND as I'm trying to find two things?? If anyone can give me a template using my information above, that would be fantastic! Thank you! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ Formula!
Yes, that IS what I'm looking for!! I had to change the COUNT part to COUNTIF
because I'm only looking to find the percentage of the number of "2"s that answered, but I could change that on my own. :) Now I have another question ... when I'm looking at people who identified as a '2' -- I'm actually looking at 3 columns for those numbers (range 4:100). How do I put that into the equation ... I tried to just write =SUMPRODUCT(--(V4:W100=2),--(E4:E100=5)) but it said the Value was wrong ... so I tried to put V4:V100, W4:W100, X4:X100 and that didn't work either. I'm guessing I have to put them each in on their own, but I don't know entirely where they go in regard to parenthesis and funny dashes. :) Thank you so much!! Amber "Dave Peterson" wrote: =sumproduct(--(a2:a99=2),--(b2:b99=5)) will give the number of rows that have 2's in column A and at the same time 5's in column B. Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ============ If you're using xl2007, there's a new =countifs() function that will do the same kind of thing. ======= To determine the percentage, you can divide this total by the overall total (I think that's what you want). =Counta(a2:a99) will return the number on non-empty cells in that range. So maybe... =sumproduct(--(a2:a99=2),--(b2:b99=5)) / counta(a2:a99) is what you want (and format as a percentage). alh06 wrote: This is what I am trying to find: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- I have no clue what formula to use - I tried using COUNTIF combined with IF and thought it was right, but now certain numbers aren't working out correctly. Then i thought maybe I needed to use AND as I'm trying to find two things?? If anyone can give me a template using my information above, that would be fantastic! Thank you! -- Dave Peterson . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ Formula!
This will count the number of cells in V4:W4 (both columns) that have 2's in
them while column E has a 5: =SUMPRODUCT((V4:W100=2)*(E4:E100=5)) This will count the number of rows that match: =SUMPRODUCT(--(E4:E100=5),--(((V4:V100=2)+(W4:W100)=2)0)) alh06 wrote: Yes, that IS what I'm looking for!! I had to change the COUNT part to COUNTIF because I'm only looking to find the percentage of the number of "2"s that answered, but I could change that on my own. :) Now I have another question ... when I'm looking at people who identified as a '2' -- I'm actually looking at 3 columns for those numbers (range 4:100). How do I put that into the equation ... I tried to just write =SUMPRODUCT(--(V4:W100=2),--(E4:E100=5)) but it said the Value was wrong ... so I tried to put V4:V100, W4:W100, X4:X100 and that didn't work either. I'm guessing I have to put them each in on their own, but I don't know entirely where they go in regard to parenthesis and funny dashes. :) Thank you so much!! Amber "Dave Peterson" wrote: =sumproduct(--(a2:a99=2),--(b2:b99=5)) will give the number of rows that have 2's in column A and at the same time 5's in column B. Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ============ If you're using xl2007, there's a new =countifs() function that will do the same kind of thing. ======= To determine the percentage, you can divide this total by the overall total (I think that's what you want). =Counta(a2:a99) will return the number on non-empty cells in that range. So maybe... =sumproduct(--(a2:a99=2),--(b2:b99=5)) / counta(a2:a99) is what you want (and format as a percentage). alh06 wrote: This is what I am trying to find: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- I have no clue what formula to use - I tried using COUNTIF combined with IF and thought it was right, but now certain numbers aren't working out correctly. Then i thought maybe I needed to use AND as I'm trying to find two things?? If anyone can give me a template using my information above, that would be fantastic! Thank you! -- Dave Peterson . -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help w/ Formula!
Yea! Both formulas worked ... was that supposed to happen? When I read your
explanation of each, I'm not sure what the difference is. Trying them both, they gave the same answer ... so I just want to be sure I'm using the correct one in case there is a difference I'm unaware of. Thank you soo much Dave! This has been driving me crazy the entire week at work! Amber "Dave Peterson" wrote: This will count the number of cells in V4:W4 (both columns) that have 2's in them while column E has a 5: =SUMPRODUCT((V4:W100=2)*(E4:E100=5)) This will count the number of rows that match: =SUMPRODUCT(--(E4:E100=5),--(((V4:V100=2)+(W4:W100)=2)0)) alh06 wrote: Yes, that IS what I'm looking for!! I had to change the COUNT part to COUNTIF because I'm only looking to find the percentage of the number of "2"s that answered, but I could change that on my own. :) Now I have another question ... when I'm looking at people who identified as a '2' -- I'm actually looking at 3 columns for those numbers (range 4:100). How do I put that into the equation ... I tried to just write =SUMPRODUCT(--(V4:W100=2),--(E4:E100=5)) but it said the Value was wrong ... so I tried to put V4:V100, W4:W100, X4:X100 and that didn't work either. I'm guessing I have to put them each in on their own, but I don't know entirely where they go in regard to parenthesis and funny dashes. :) Thank you so much!! Amber "Dave Peterson" wrote: =sumproduct(--(a2:a99=2),--(b2:b99=5)) will give the number of rows that have 2's in column A and at the same time 5's in column B. Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ============ If you're using xl2007, there's a new =countifs() function that will do the same kind of thing. ======= To determine the percentage, you can divide this total by the overall total (I think that's what you want). =Counta(a2:a99) will return the number on non-empty cells in that range. So maybe... =sumproduct(--(a2:a99=2),--(b2:b99=5)) / counta(a2:a99) is what you want (and format as a percentage). alh06 wrote: This is what I am trying to find: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- I have no clue what formula to use - I tried using COUNTIF combined with IF and thought it was right, but now certain numbers aren't working out correctly. Then i thought maybe I needed to use AND as I'm trying to find two things?? If anyone can give me a template using my information above, that would be fantastic! Thank you! -- Dave Peterson . -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|