Percentage (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

See the attachment and clean the cells what you dont want (sheet2)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Wow – this is great. Thank you. I will try it tonight.

Again a HUGE thank you..

Reply | Quote

Look at this example. Notice the formula in “Col L” I just added. It combines the code class etc and question for the label. The label box only displays 1 label so it acts like a title.

Reply | Quote

OK – this is where I show how dumb I really am. How do I get other courses to display?

Reply | Quote

If I understand the problem correctly:
Click on the the little “pulldown” arrow in the “header row” and select a different one. Also select a different question if desired the same way.

Otherwise I don’t understand your question.

Steve

Reply | Quote

Hi – I am back. Spent some time working on the other semesters. I had trouble figuring out how to add in the other semesters to the spreadsheet you created so I just copied and pasted everything into a new file. Of course since I don’t know exactly what I am doing, I cannot get the chart to display the correct information. Can you take a look a what I have so far? I do not know if all of the data should be in one spreadsheet? I have a separate file for each semester.

Reply | Quote

There is no chart in the attachment to comment on.
What is the “correct information” that you are trying to chart?
I would put all the data in one spreadsheet. If you want to extract certain classes you can use the filters I set up.

I notice you did NOT calculate “NR” in this spreadsheet. I calculated the number of enrollees – number of responses to A-E. If that is NOT correct you might want to look at the percentage calc. %A is #A / #(A+B+E+D+E). The %NR (I calculate) as #NR/#Enrollees. You might want to rethink if NR is different than what I used. DO you want , eg, %A = #A/#(A+B+E+D+E+NR) so that %NR is #NR/(A+B+E+D+E+NR)

Steve

Reply | Quote

I need what you already did for me but for all the semesters. I guess I wasn’t sure how to add the new semesters so I just highlighted the worksheet you did for me and then copied and pasted it into a new workbook. I didn’t really do anything to the formulas though. I just deleted the responses and added the new responses and the formulas appeared to have worked. I didn’t check it though.
I didn’t know how to get the chart working. I need every response to question #1 to be one chart. Then every response for #2 to be a separate chart and so on. I think you did this for me on the second file you attached.
I did look at the filters you made and they worked wonderfully. I just didn’t know how to get the rest of the data into that spreadsheet. Please except my apologies for being so dense.

Reply | Quote

First MOVE cell L2 to L1 (you need to only do this, this one time). It makes the chart wizard work better!

Next, Copy all the new semesters to the bottom of the current list.

Select the chart sheet and on the menu pick Chart – Source data – “data range” tab. and change the range from:
=Redo!$L$2:$R$CurrentLastrow#
to:
=Redo!$L$2:$R$Newlastrow#

Now the filter should work as before
Steve

Reply | Quote

OK – so I should open the spreadsheet you made for me with the chart in it and then copy and paste everything I did into this file.

thank you again

Reply | Quote

THANK YOU STEVE. I worked several hours last night on the project. I finally have everything entered and I am getting more familiar with the filters and charts. This is wonderful. I am presenting everything to the professor soon.

Please accept my sincere appreciation.

Melanie

Reply | Quote

You are welcome. I’m happy that I could help.

Steve

Reply | Quote

Steve, I’m back. The professor loved the spreadsheet and the charts and the ease of creating new charts. While I was working on her spreadsheet, a colleague showed her another way to present the data and she wants her document to look like his. I created a table in Word (because she uses Word for the rest of the document) and inserted two columns, the first column has the questions and the second has the Excel Chart. I cannot get her charts to import and look like his. I noticed he has different legends and I tried to change this in Excel and I could not get it to work. I think it has something to do with the data series.

I tried attaching a jpg of the paper she was given but it but the file in the body of this message instead of as an attachment. Not sure what I am to do now.
If I used up all your time before, I understand. You have already been more help then I could ever hope for.

Reply | Quote

You didn’t attach the jpg file

Steve

Reply | Quote

Thank you.

Reply | Quote

(Modified POST added other info to workbook)

I added a new chart (like you listed). It sums ALL the respondents to each question. The Spinner button changes the question number.

The original chart still lists INDIVIDUAL questions for INDIVIDUAL classes. The 2nd chart lists INDIVIDUAL questions for ALL classes on the data sheet.

Steve

Reply | Quote

WOW!!! I am amazed. Are you this good with Microsoft Word?

Thank you a million times over.

Reply | Quote

No, I never use Microsoft WORD anymore since our company went to email for documentation instead of writing memos.

Another thing I thought of, is that instead of PICKING each of the 17 questions, you could make the output of ALL 17 questions in an excel sheet and have all the charts on it (instead of doing it in WORD). This should be very simple to do. As new data was added the charts would always be current.

You are welcome a million times over.
Steve

Reply | Quote

Hello Steve, big surprise, it is me again. I was finally able to work on this project again and everything was going smoothly. I was copying the chart for each question and pasting it in Word in a table next to the appropriate question, however the file was huge and I was unable to save to a floppy, I did not have access to a zip drive. So I created a new spreadsheet and started copying and pasting each chart. Problem is when I used the spinner for a new question, the chart automatically changed in my other file to match whatever question I was on. I know you probably wish to be rid of me. sorry! (love the spinner button though, thank you). I have attached what I was trying to do. I cannot attach the entire spreadsheet because it is 231k.

Reply | Quote

I wondered when you were going to ask for this. I suggested it 2 weeks ago.

I started to try to explain how to do it, and it gave up so I just made a new sheet on the last one I did. I kept the live one where you scroll thru the questions.

Steve

Reply | Quote