Results 1 to 15 of 33
Thread: Percentage (Excel 2000)

20020827, 12:54 #1
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Percentage (Excel 2000)
I need some help with a spreadsheet for a faculty member. She is calculating her evaluations. Here is the info.
A department number, course number, section number  5210101
# enrolled  50
# responding  40
17 questions 
AE answers
I have the info for how many students answered which questions, maybe 10 answered A, 15 answered B and so on.
Now I have to find the percentage of people answering A, B, C, D, E.
Once I have this information, I have to calculate it for each question for every course for each semester for 3 years.
Can anyone help me please??

20020827, 15:59 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Percentage (Excel 2000)
A pivot table might be the answer.
Could you post a sample spreadsheet with some dummy data? It shouldn't be over 100K (if it is  I hope not  you can zip it and attach the zip file).

20020827, 16:10 #3
 Join Date
 Mar 2002
 Location
 Western NY, USA
 Posts
 157
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
I would use array formula to solve this problem. If you post a 10 to 15 line sample, I believe I can provide what you are looking for. From your description, I cannot visualize what you have, i.e., what is a semester? Is it derived from the section number?

20020827, 16:12 #4
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
I might not have the full picture of your problem, but the first approach to me would be to create a table where you enter each person's name (in column A, say), then create a column for each other item of information. For example, column B would be Enrolled?; column C would be Responding?; Column D would be Question 1; Column E would be Question 2 and so on. Then enter a "1" (no quotes) in each cell for each person that fits the category. So, for example, if Person 3 were in cell A4 and answered Questions 1, 2 and 3, then you would put a 1 in cells D4, E4 and F4.
At the bottom of each category column, you could use a SUM function to total up the number of people who responded in each category and then compute the averages. So, for example, you have 50 people enrolled, and your SUM for Question 1 is 28. You divide 28 by 50 and format the result as a percentage. In this example, you would report that 56% of the people responded to Question 1.
Assuming I interpreted your conditions correctly, this is how I'd start...

20020827, 18:23 #5
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
OK  here is a copy. I left the percentage area blank when I filled in the responses. So for each question I needed to figure out the percentage of respondents. I am sorry this is so vague but I do not use Excel very often. Thanks

20020827, 19:27 #6
 Join Date
 Jan 2002
 Location
 Tampico, Tamps, Mexico
 Posts
 118
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
See the attachment and clean the cells what you dont want (sheet2)

20020827, 22:21 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Percentage (Excel 2000)
I revamped your spreadsheet in a way more like I would do it. Your setup was very cumbersome.
I used Autofiltering to "pick a class" and calculate each question along one row. You can see much more of you data this way. the shaded regions are calculations. I assumed NR was "No Response" and calculated the number of enrolled people who did not answer the question.
The percentages are based on the %responses not the %enrolled (you can chanbe easily enough). The %NR is the % No responders out of the total enrolled (aagain, change as desired)
I used conditional formatting to "lighten" the dupes to make it easier to read
Steve

20020827, 23:25 #8
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
Steve,
I was going to respond in a similar fashion to Melanie's problem, but my response would not have been as elegant as yours. Yours looks like it fits the bill nicely.
George

20020828, 14:39 #9
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
Wow  thank you  I am not sure which post I am responding to. I only saw one attachment so I opened it. One more question. How do I get all of the other data in this format? Is it easy to copy and paste it? I have 4 other spreadsheets each with at least 4 worksheets (for each of the courses taught during the semester). It is in the same cumbersome format that I began with.

20020828, 16:14 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Percentage (Excel 2000)
You could just delete the extraneous rows (not too bad if you don't have alot)
If you have alot you might try autofilter and then delete rows without numbers in the first column
[select column a then Data filter  autofilter
Select pulldown  custom begins with "Q", Delete rows with "Question x"
Select pulldown  custom begins with "a", Delete rows with A, B, Etc
Select pulldown  (Blanks), Delete blank rows]
Then insert the columns for the code/class/semester/enrolle#
Move the data in the top row and copy it to the other rows of the data.
Move it into the master datasheet
Repeat for all new entries
Select on of the rows of the "formatted" data and copy the format thru the area to set the conditional formatting and such
Select the "gray region" and copy it down thru the new data to add the calcs
Steve

20020829, 13:22 #11
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
One last request  the professor now wants the total number of responses for each question for every student in every section displayed as a chart. This is possible but I am just not sure how to do it with the fancy new spreadsheet that you created for me.
Thanks

20020829, 14:10 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Percentage (Excel 2000)
I am unclear on what you are asking. You don't have any student data. Are you keeping track of each student's response to each question?
Do you just want to plot total responses vs each Class? in a bar/column chart? in a line chart?
Do you want totals for each of question (all classes combined)?
We need more to go on.
Steve

20020829, 14:18 #13
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
Sorry  Yes I need to keep track of each student's response, so you are correct, there is not any data on an actual student.
Bar/column chart. She needs to be able to present it for all classes. So for example, question #1 for each section of each course for each semester, could possibly have over 100 responses divided out amongst a, b, c, d, e and n/r for no response. This would be one chart. Then question #2 would have it's own chart and so on down to #17.
I hope this makes sense. It is obvious that I really do not know exactly how to explain myself!
Thank you

20020829, 18:05 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Percentage (Excel 2000)
If you based a chart on the WHOLE data set, when you would use the Auto filter to select a semester, class, question, student, etc the chart would "collapse" to only plot the "Visible data".
You could have all your charts from this one chart, just make different selections in the Autofilter.
Steve
Steve

20020829, 18:08 #15
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Percentage (Excel 2000)
Wow  this is great. Thank you. I will try it tonight.
Again a HUGE thank you..