MS Excel question re: Percentages

I have a very simple spreadsheet. I’m trying to show the improvement of kids taking a quiz before and after a lesson (the first quiz being a control to show that they didn’t already know the material).

The spreadsheet should have, in the end, the following data:

Column A: question numbers (1-10)
Column B: number of correct answers for that question on the pre-quiz
Column C: percentage of kids answering that question right on the pre-quiz
Column D: number of correct answers for that question on the post-quiz
Column E: percentages of kids answering that question right on the post-quiz
Column F: difference between Columns D and B (how many more kids got the question right on the post quiz than on the pre-quiz)
Column G: percentage increase from the pre-quiz to the post-quiz

So there were 20 kids taking the quiz, which means that I could find the percentage by multiplying the number of correct answers by five. So for Column C, for example, I put =B1*5. The number of kids getting that question right was 2, so 2*5 was 10, which was the correct percent. I selected the cell to format it as a percentage and now it says “1000%
Buh? I don’t unnersan’. How do I do it?

=(b1*5)/100

=(b1*5)/100

Alternatively, you could use =b1/20

Alternatively to that, you could have a reference cell someplace for each quiz with how many quiz-takers there were (in case of absences or additions to the class), and reference that cell instead of manually inputting the number each time.

This is a one-time thing. It’s for a paper I need to write. There will be no future quizzes or classes, and all of the data is already entered in.

[For the one person out there who may be curious, I taught three classes of 5th graders the same information. The first class got only a lecture. The second got a lecture with a powerpoint presentation that had visual aids and written text. The third class had the powerpoint and also a hands-on activity. The paper will discuss how the more stimuli you bring into a lesson, the more the information is retained. Whee.]

Ok so the =b1/20 is the magical formula that I knew was out there. (I am math-phobic, haven’t taken math in about 20 years, and my brain turns off when I have to come up with anything involving numbers. It’s sad, really.)

Ten-Q!

Sounds like you’ve got your answer (I guess we can assume you weren’t teaching math :wink: ). When you have a number in a cell, and then change the format to %, Excel interprets the number as a fraction that is supposed to be displayed as a %, e.g., 0.5 is displayed as 50%. That’s why 10 was displayed as 1000%.

(However, note that once you format the cell as %, you can type in “10” and it will be displayed as 10%. That doesn’t apply in your case since you’re using formulas but it’s something to be aware of.)

And since we’re on this tangent, you might want to be aware that if you type 10 into a cell formatted as %, it will display 10%, and the numeric value stored in the cell will be 0.1. (I’ve seen people bit by that in formulae, so I thought I’d mention it.)