Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
I have been working on a summary page for reviewing some internal surveys we
do and am hopping soneone might have a simpler solution then what I am having to do. The data is in a table that has the month the survey was done and the answers to the questions next to it. We have a 1 to 5 answer system, with 5 being best. It was not to hard to get the average score for each question in a month, but finding a "Top Two" box total for each month was a bit more chalanging. Top Two Box, if you don't know, is where you take and count up only the answers that got a 4 or a 5 for each month and then place that over a total questions asked for the month to get a %. I have 13 questions and for my solution I took the time to create a sperate hidden sheet that has several DCOUNT and DCOUNT criteria tables. I had to make a query for each question in each month, then total that... it works, but I have to guess I missed a way to do this much easier. Now I know that if I could have a count on each line, it would be much easier, but in this case it is not an option. The users will be inputting data into the table row by row, and in time deleting the older rows. So, a brief example data file: (starting in A1) month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month2 1 2 3 4 5 1 2 3 4 5 month2 5 4 5 4 5 4 5 4 5 4 So the Top Two for month1 would be a count of 6 and for month 2 would be 14 The summary page has a collum for each month with the vaule of the month (month1, month2) at the top. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
So the Top Two for month1 would be a count of 6 and for month 2 would be 14
Can you explain how you arrive at those figures. For month1 there are a total of 12 entries that are either 4 or 5 For month2 there are a total of 14 entries that are either 4 or 5 Are you wanting to count the 4's and 5's for each month? Sheet2 A1 = month1 Sheet2 B1 = month2 Enter this formula in Sheet2 cell A2: =SUMPRODUCT((Sheet1!$A1:$A5=A1)*((Sheet1!$B1:$K5=4 )+(Sheet1!$B1:$K5=5))) Copy across to B2 Biff "Brian H" wrote in message news:f2NDh.1128557$R63.792734@pd7urf1no... I have been working on a summary page for reviewing some internal surveys we do and am hopping soneone might have a simpler solution then what I am having to do. The data is in a table that has the month the survey was done and the answers to the questions next to it. We have a 1 to 5 answer system, with 5 being best. It was not to hard to get the average score for each question in a month, but finding a "Top Two" box total for each month was a bit more chalanging. Top Two Box, if you don't know, is where you take and count up only the answers that got a 4 or a 5 for each month and then place that over a total questions asked for the month to get a %. I have 13 questions and for my solution I took the time to create a sperate hidden sheet that has several DCOUNT and DCOUNT criteria tables. I had to make a query for each question in each month, then total that... it works, but I have to guess I missed a way to do this much easier. Now I know that if I could have a count on each line, it would be much easier, but in this case it is not an option. The users will be inputting data into the table row by row, and in time deleting the older rows. So, a brief example data file: (starting in A1) month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month2 1 2 3 4 5 1 2 3 4 5 month2 5 4 5 4 5 4 5 4 5 4 So the Top Two for month1 would be a count of 6 and for month 2 would be 14 The summary page has a collum for each month with the vaule of the month (month1, month2) at the top. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
=SUMPRODUCT((ISNUMBER(MATCH(B1:K5,{4,5},0)))*(A1:A 5="Month1"))
=SUMPRODUCT((ISNUMBER(MATCH(B1:K5,{4,5},0)))*(A1:A 5="Month2")) "Brian H" wrote: I have been working on a summary page for reviewing some internal surveys we do and am hopping soneone might have a simpler solution then what I am having to do. The data is in a table that has the month the survey was done and the answers to the questions next to it. We have a 1 to 5 answer system, with 5 being best. It was not to hard to get the average score for each question in a month, but finding a "Top Two" box total for each month was a bit more chalanging. Top Two Box, if you don't know, is where you take and count up only the answers that got a 4 or a 5 for each month and then place that over a total questions asked for the month to get a %. I have 13 questions and for my solution I took the time to create a sperate hidden sheet that has several DCOUNT and DCOUNT criteria tables. I had to make a query for each question in each month, then total that... it works, but I have to guess I missed a way to do this much easier. Now I know that if I could have a count on each line, it would be much easier, but in this case it is not an option. The users will be inputting data into the table row by row, and in time deleting the older rows. So, a brief example data file: (starting in A1) month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month2 1 2 3 4 5 1 2 3 4 5 month2 5 4 5 4 5 4 5 4 5 4 So the Top Two for month1 would be a count of 6 and for month 2 would be 14 The summary page has a collum for each month with the vaule of the month (month1, month2) at the top. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
I can't count... :) Good eyes... (it was a long day)
"T. Valko" wrote in message ... So the Top Two for month1 would be a count of 6 and for month 2 would be 14 Can you explain how you arrive at those figures. For month1 there are a total of 12 entries that are either 4 or 5 For month2 there are a total of 14 entries that are either 4 or 5 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
This does work great in the test sample, but my real life sample has an
unknown number of surveys so the grid's depth is not known. If I try these great formula with collum referances (Sheet1!$A:$A) it only returns #NUM. I get an #N/A if I use a referance that would cover the maximum known surveys (Sheet1!$A1:$A5000) as there is no data in the current cells. My thanks to both you and Mama for a new twist to add to my next project... :-) "T. Valko" wrote in message ... So the Top Two for month1 would be a count of 6 and for month 2 would be 14 Can you explain how you arrive at those figures. For month1 there are a total of 12 entries that are either 4 or 5 For month2 there are a total of 14 entries that are either 4 or 5 Are you wanting to count the 4's and 5's for each month? Sheet2 A1 = month1 Sheet2 B1 = month2 Enter this formula in Sheet2 cell A2: =SUMPRODUCT((Sheet1!$A1:$A5=A1)*((Sheet1!$B1:$K5=4 )+(Sheet1!$B1:$K5=5))) Copy across to B2 Biff "Brian H" wrote in message news:f2NDh.1128557$R63.792734@pd7urf1no... I have been working on a summary page for reviewing some internal surveys we do and am hopping soneone might have a simpler solution then what I am having to do. The data is in a table that has the month the survey was done and the answers to the questions next to it. We have a 1 to 5 answer system, with 5 being best. It was not to hard to get the average score for each question in a month, but finding a "Top Two" box total for each month was a bit more chalanging. Top Two Box, if you don't know, is where you take and count up only the answers that got a 4 or a 5 for each month and then place that over a total questions asked for the month to get a %. I have 13 questions and for my solution I took the time to create a sperate hidden sheet that has several DCOUNT and DCOUNT criteria tables. I had to make a query for each question in each month, then total that... it works, but I have to guess I missed a way to do this much easier. Now I know that if I could have a count on each line, it would be much easier, but in this case it is not an option. The users will be inputting data into the table row by row, and in time deleting the older rows. So, a brief example data file: (starting in A1) month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month2 1 2 3 4 5 1 2 3 4 5 month2 5 4 5 4 5 4 5 4 5 4 So the Top Two for month1 would be a count of 6 and for month 2 would be 14 The summary page has a collum for each month with the vaule of the month (month1, month2) at the top. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
I have been looking at this one for a while, still not sure why it works on
the test data... :-) As I said before, in the real data it fails, if I set the month check to a column reference I get #NUM, when I do column references for the Match, I get 0 (zero). Thanks for the good ideas... "Teethless mama" wrote in message ... =SUMPRODUCT((ISNUMBER(MATCH(B1:K5,{4,5},0)))*(A1:A 5="Month1")) =SUMPRODUCT((ISNUMBER(MATCH(B1:K5,{4,5},0)))*(A1:A 5="Month2")) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
If I try these great formula with collum referances (Sheet1!$A:$A) it only
returns #NUM. You can't use entire columns as range references with Sumproduct (unless you're using Excel 2007). I just thought of something. If the highest rating is 5 and you want to count 4's and 5's then all we need to do is get the count that is greater than or equal to 4: =SUMPRODUCT((Sheet1!$A1:$A5=A1)*(Sheet1!$B1:$K5=4 )) Now, about the #N/A........ Is "month1" really a DATE or is it a TEXT string as you have represented it? That's why it's best to tell us *exactly* what you have instead of making stuff up! As far as not knowing how big the range is, just use a larger range reference that you know you will never exceed, or, use a dynamic range. See this on how to create a dynamic named range: http://contextures.com/xlNames01.html#Dynamic Biff "Brian H" wrote in message news:XoZDh.1123526$5R2.149498@pd7urf3no... This does work great in the test sample, but my real life sample has an unknown number of surveys so the grid's depth is not known. If I try these great formula with collum referances (Sheet1!$A:$A) it only returns #NUM. I get an #N/A if I use a referance that would cover the maximum known surveys (Sheet1!$A1:$A5000) as there is no data in the current cells. My thanks to both you and Mama for a new twist to add to my next project... :-) "T. Valko" wrote in message ... So the Top Two for month1 would be a count of 6 and for month 2 would be 14 Can you explain how you arrive at those figures. For month1 there are a total of 12 entries that are either 4 or 5 For month2 there are a total of 14 entries that are either 4 or 5 Are you wanting to count the 4's and 5's for each month? Sheet2 A1 = month1 Sheet2 B1 = month2 Enter this formula in Sheet2 cell A2: =SUMPRODUCT((Sheet1!$A1:$A5=A1)*((Sheet1!$B1:$K5=4 )+(Sheet1!$B1:$K5=5))) Copy across to B2 Biff "Brian H" wrote in message news:f2NDh.1128557$R63.792734@pd7urf1no... I have been working on a summary page for reviewing some internal surveys we do and am hopping soneone might have a simpler solution then what I am having to do. The data is in a table that has the month the survey was done and the answers to the questions next to it. We have a 1 to 5 answer system, with 5 being best. It was not to hard to get the average score for each question in a month, but finding a "Top Two" box total for each month was a bit more chalanging. Top Two Box, if you don't know, is where you take and count up only the answers that got a 4 or a 5 for each month and then place that over a total questions asked for the month to get a %. I have 13 questions and for my solution I took the time to create a sperate hidden sheet that has several DCOUNT and DCOUNT criteria tables. I had to make a query for each question in each month, then total that... it works, but I have to guess I missed a way to do this much easier. Now I know that if I could have a count on each line, it would be much easier, but in this case it is not an option. The users will be inputting data into the table row by row, and in time deleting the older rows. So, a brief example data file: (starting in A1) month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month2 1 2 3 4 5 1 2 3 4 5 month2 5 4 5 4 5 4 5 4 5 4 So the Top Two for month1 would be a count of 6 and for month 2 would be 14 The summary page has a collum for each month with the vaule of the month (month1, month2) at the top. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Top Two Survey Results
The months are dates, the first of the month to be exact, formatted to show
Month and Year (Feb-07) So, the lighting bolt of "dynamic range" woke me up, it's been sooooo long since I had used one. Yes, that fixed both of the errors, and lets me remove almost half the size of the template file. Great advice! Now onto my "extra" feature I wish to add... a list of all comments from the survey data at the bottom of the summary page. But that's another topic... "T. Valko" wrote in message ... If I try these great formula with collum referances (Sheet1!$A:$A) it only returns #NUM. You can't use entire columns as range references with Sumproduct (unless you're using Excel 2007). I just thought of something. If the highest rating is 5 and you want to count 4's and 5's then all we need to do is get the count that is greater than or equal to 4: =SUMPRODUCT((Sheet1!$A1:$A5=A1)*(Sheet1!$B1:$K5=4 )) Now, about the #N/A........ Is "month1" really a DATE or is it a TEXT string as you have represented it? That's why it's best to tell us *exactly* what you have instead of making stuff up! As far as not knowing how big the range is, just use a larger range reference that you know you will never exceed, or, use a dynamic range. See this on how to create a dynamic named range: http://contextures.com/xlNames01.html#Dynamic Biff "Brian H" wrote in message news:XoZDh.1123526$5R2.149498@pd7urf3no... This does work great in the test sample, but my real life sample has an unknown number of surveys so the grid's depth is not known. If I try these great formula with collum referances (Sheet1!$A:$A) it only returns #NUM. I get an #N/A if I use a referance that would cover the maximum known surveys (Sheet1!$A1:$A5000) as there is no data in the current cells. My thanks to both you and Mama for a new twist to add to my next project... :-) "T. Valko" wrote in message ... So the Top Two for month1 would be a count of 6 and for month 2 would be 14 Can you explain how you arrive at those figures. For month1 there are a total of 12 entries that are either 4 or 5 For month2 there are a total of 14 entries that are either 4 or 5 Are you wanting to count the 4's and 5's for each month? Sheet2 A1 = month1 Sheet2 B1 = month2 Enter this formula in Sheet2 cell A2: =SUMPRODUCT((Sheet1!$A1:$A5=A1)*((Sheet1!$B1:$K5=4 )+(Sheet1!$B1:$K5=5))) Copy across to B2 Biff "Brian H" wrote in message news:f2NDh.1128557$R63.792734@pd7urf1no... I have been working on a summary page for reviewing some internal surveys we do and am hopping soneone might have a simpler solution then what I am having to do. The data is in a table that has the month the survey was done and the answers to the questions next to it. We have a 1 to 5 answer system, with 5 being best. It was not to hard to get the average score for each question in a month, but finding a "Top Two" box total for each month was a bit more chalanging. Top Two Box, if you don't know, is where you take and count up only the answers that got a 4 or a 5 for each month and then place that over a total questions asked for the month to get a %. I have 13 questions and for my solution I took the time to create a sperate hidden sheet that has several DCOUNT and DCOUNT criteria tables. I had to make a query for each question in each month, then total that... it works, but I have to guess I missed a way to do this much easier. Now I know that if I could have a count on each line, it would be much easier, but in this case it is not an option. The users will be inputting data into the table row by row, and in time deleting the older rows. So, a brief example data file: (starting in A1) month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month1 1 2 3 4 5 1 2 3 4 5 month2 1 2 3 4 5 1 2 3 4 5 month2 5 4 5 4 5 4 5 4 5 4 So the Top Two for month1 would be a count of 6 and for month 2 would be 14 The summary page has a collum for each month with the vaule of the month (month1, month2) at the top. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Survey Results help | Excel Worksheet Functions | |||
Averaging survey results | Excel Worksheet Functions | |||
survey results | Excel Worksheet Functions | |||
survey results tally | Excel Worksheet Functions | |||
Tabulating Survey Results | Excel Discussion (Misc queries) |