# MS Excel question re: Percentages **URL:** \ **Category:** Factual Questions\ **Created:** [November 29, 2006, 3:39am UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165 "2006-11-29T03:39:52Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![OpalCat](https://avatars.discourse-cdn.com/v4/letter/o/8edcca/32.png) [@OpalCat](https://boards.straightdope.com/u/OpalCat)\ **Post date:** [November 29, 2006, 3:39am UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165/1 "2006-11-29T03:39:52Z")
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? ---
**Author:** ![Harriet\_the\_Spry](https://avatars.discourse-cdn.com/v4/letter/h/e56c9b/32.png) [@Harriet\_the\_Spry](https://boards.straightdope.com/u/Harriet_the_Spry)\ **Post date:** [November 29, 2006, 3:51am UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165/2 "2006-11-29T03:51:03Z")
=(b1\*5)/100 ---
**Author:** ![Garfield226](https://avatars.discourse-cdn.com/v4/letter/g/9e8a1a/32.png) [@Garfield226](https://boards.straightdope.com/u/Garfield226)\ **Post date:** [November 29, 2006, 3:52am UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165/3 "2006-11-29T03:52:03Z")
=(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. ---
**Author:** ![OpalCat](https://avatars.discourse-cdn.com/v4/letter/o/8edcca/32.png) [@OpalCat](https://boards.straightdope.com/u/OpalCat)\ **Post date:** [November 29, 2006, 4:55am UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165/4 "2006-11-29T04:55:27Z")
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.] ---
**Author:** ![OpalCat](https://avatars.discourse-cdn.com/v4/letter/o/8edcca/32.png) [@OpalCat](https://boards.straightdope.com/u/OpalCat)\ **Post date:** [November 29, 2006, 4:56am UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165/5 "2006-11-29T04:56:59Z")
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! ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [November 29, 2006, 3:00pm UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165/6 "2006-11-29T15:00:07Z")
Sounds like you’ve got your answer (I guess we can assume you weren’t teaching math 😉 ). 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.) ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [November 29, 2006, 3:30pm UTC](https://boards.straightdope.com/t/ms-excel-question-re-percentages/382165/7 "2006-11-29T15:30:37Z")
[QUOTE=CookingWithGas] (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.) [/QUOTE] 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.)