Finding Top Two Survey Results

[Brian H]

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.

 

[T. Valko]

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

 

[Guest]

=SUMPRODUCT((ISNUMBER(MATCH(B1:K5,{4,5},0)))*(A1:A5="Month1"))

=SUMPRODUCT((ISNUMBER(MATCH(B1:K5,{4,5},0)))*(A1:A5="Month2"))

 

[Brian H]

I can't count... Good eyes... (it was a long day)

 

[Brian H]

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...

 

[Brian H]

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...

 

[T. Valko]

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]

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...