Donate SIGN UP

Excel lookup

Avatar Image
funkylad20 | 16:03 Wed 25th May 2011 | Computers
7 Answers
I have a questionnaire with 17 questions. The answer for each question is one of the following; 'very satisfied', 'satisfied', 'neither satisfied nor dissatisfied', 'dissatisfied', 'very dissatisfied' and 'did not use'.

I have labelled each question numerically, 1-17.

The results are fed through with each question on the left column (A) and a 1 or a 0 in columns B - H, depending on where the questionnaire candidate put their response.

I want to somehow collate all of the responses to form pie charts for each question individually.

Any suggestions? This is not a one off exercise, so need to establish a set area on a different tab to feed through the results at the press of a refresh button!
Gravatar

Answers

1 to 7 of 7rss feed

Best Answer

No best answer has yet been selected by funkylad20. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Just to clarify, you say you have 1 or 0 in the columns depending upon the replies. When the next reply comes in does the 1 become 2 and so on?

I'm imagining a row per question with the final reults along eacg row readng like this for example.

26 very satisfied, 20 satisfied, 8 niether, 1 dissatisfied, 0 very dis, 7 did not used.

If so, hightlight that row and click on the graph wizard. Follow the wizard through (ask again if ytou need help) and save the graph as a new tab called Q1 graph.
Question Author
The 1 entry will be the cross in the box, or the yes, the one agreed strongest statement. The 0 will just be the others. Each question can only have one response, so one '1' per question.

I need to use countif / if / sumif I think, but think it needs to be an amalgamation of two.

In normal terms, I want to say 'lookup and see if a row refers to question '1', then feed the results of that row from column'.
Question Author
Sussed it!

=SUMIF(Data!$B:$B,"1",Data!D:D)
I thought if you highlighted the data in your table you had a menu option for a 'wizard' that would guide you through making charts & graphs of whatever desired sort.
Tsk 2 replies that weren't there when I started typing !
Tsk tsk tsk I mean 3 posts that weren't there when I started reading !
Pleased you've sussed it. For future reference, if you want to count rather than add, just replace the word Sumif with Count. And countif to only count certain cells. eg to count the cells which contain and x use.............
=COUNTIF(B2:B21,"x")

1 to 7 of 7rss feed

Do you know the answer?

Excel lookup

Answer Question >>