Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    3 Star Lounger
    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 - 52-101-01
    # enrolled - 50
    # responding - 40
    17 questions -
    A-E 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??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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).

  3. #3
    2 Star Lounger
    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?

  4. #4
    2 Star Lounger
    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...

  5. #5
    3 Star Lounger
    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
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    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)

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    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

  9. #9
    3 Star Lounger
    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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    3 Star Lounger
    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

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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

  13. #13
    3 Star Lounger
    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

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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

  15. #15
    3 Star Lounger
    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..

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •