Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Survey Results help Curt1521 Excel Worksheet Functions 1 December 19th 06 12:16 AM
Averaging survey results jmstut Excel Worksheet Functions 1 December 6th 06 05:59 PM
survey results [email protected] Excel Worksheet Functions 1 December 6th 06 12:21 AM
survey results tally ME Hill Excel Worksheet Functions 3 May 30th 06 07:37 PM
Tabulating Survey Results Tabulatin Survey results Excel Discussion (Misc queries) 3 December 13th 04 10:56 PM


All times are GMT +1. The time now is 03:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"