Forum Discussion
Help with MS Forms Responses in Excel
I have created a MS Form with 35 Yes or No questions. Each Yes question has a certain numerical value that I need to tally and report on.
I did do an IF statement, and it works, but I think it will be an admin nightmare to keep doing this manually when users fill out the Form.
I'm not the smartest with formulas or automation, but am hoping someone can share their wisdom.
To automate the tallying and reporting process for your MS Forms responses in Excel, you can use a combination of Excel formulas and functions.
Here is a step-by-step guide to help you achieve this:
- Open your Excel workbook and navigate to the sheet where you want to store and analyze the form responses.
- In the first row of your sheet, create column headers for each question. For example, if your questions are labeled as Q1, Q2, Q3, etc., enter these labels in the respective columns.
- Below the question labels, in the second row, you can add the corresponding numerical values for each "Yes" answer. For example, if a "Yes" response has a value of 5, you can enter 5 in the respective column.
- In the subsequent rows, starting from the third row, the form responses will be recorded automatically.
- In a separate column, next to each response, use the following formula to calculate the numerical value based on the "Yes" or "No" response:
=IF(B3="Yes", VLOOKUP(B2, A2:D2, COLUMN(D2)-COLUMN(A2)+1, FALSE), 0)
Replace B3 with the cell reference where the response is located, B2 with the cell reference where the corresponding numerical values are located, and A2:D2 with the range that includes the question labels and numerical values.
This formula uses the VLOOKUP function to find the numerical value for the question and multiplies it by 1 if the response is "Yes" or 0 if the response is "No".
- Repeat the above formula for each question, adjusting the cell references accordingly. The values will be automatically calculated based on the responses.
- To generate a summary of the total scores, you can use the SUM function. In a separate cell, use the following formula:
=SUM(E3:E100)
Replace E3:E100 with the range that includes all the calculated values for each response.
This formula adds up all the scores from the responses and provides a total.
By following these steps, you can automate the process of tallying and reporting the numerical values for "Yes" responses in your MS Forms.
The scores will be updated automatically whenever a user fills out the form.
If it is not what is requested, I recommend adding additional information. Information such as Excel version, operating system, storage medium, etc.. A file (without sensitive data) or photo/s with a step-by-step explanation would also be advantageous.
Apart from that, I wish you much success in your project.
Hi NikolonoDE! Thank you for this response. I am trying to figure out how it will work. My current situation is:
I have a form with 36 questions, which could grow by 10 more over the next year. It's actually a process assessment.- On the first tab, the Form responses are captured with their answer of Yes or No.
- The second tab has the question number, and the value if the answer is Yes. Then I start with a formula, =IF(Form1!F5="Yes",3,0) that fills in the value for the 36 questions.
- The third tab breaks down each section of the assessment (Level 1, 1.5, etc.) Each section needs a certain value to be considered a Pass, else it Fails, =IF(D5>=5,"PASS","FAIL"),
- The answers from the Form are horizontal and begin with ID, email, start time, etc. The actual questions don't begin until column F.
I've attached some screenshots for reference. Thank you so much!!!!
As variant you may to the structured table on first tab which collects form responses few other columns. That won't destroy automatic update of this table.
In additional columns you may do calculations (using structured references) for number of Yes, PASS or not PASS, whatever. That will be updated with each new response added.
On another sheet(s) that could be summary, aggregation, dashboard, what you need to show based on table data.
