Is it possible to do calculations with Microsoft Forms results?
Hello and thank you for your time.
I am surprised I was not able to find this answer already online, but I am using Microsoft Office Online and need to do calculations automatically in a separate tab on the results of a Microsoft Forms survey. I simply want to automatically sum together certain outputs of the form automatically using the Sum() function. For example let’s say I have text outputs form columns A and B, but I want to sum together the numeric outputs of C and D together as well as E and F together for each new entry. (To clarify this example, I am not looking to sum together all C and D entries together, but instead sum C and D together once for each row/each entry)
First I found that even though the answer is listed as a "Number" under the requirements of the Form, the output into the corresponding Excel sheet has a leading apostrophe, making this number into text. I was able to get around this but creating another tab and multiplying each cell by 1 to convert them into numbers.
I am able to do calculations with the numbers in this new tab however then I found that when a new submission to the Microsoft Form is completed, this adds a new row to the corresponding spreadsheet. Therefore my formula in the new tab of multiplying results by 1 skips this newly created row!
I then tried some dynamic array formulas just because those would grab results from a whole column so I thought that would negate the new row problem. For example I tried the "Unique()" formula to grab then values from each column of the auto-populated form tab, then I was going to multiply these by 1 if needed. However, the "Unique()" formula did not exist for Microsoft Online... Neither did the "Filter()" or "SortBy()"...
Can you please help me to create a tab that can run calculations automatically on the results generated by Microsoft Forms in another tab?
21 Answer
It is two years after this post, but I hit this problem and found a simple solution online here to part of this problem - the part about the apostrophe place in front of numbers in Excel Sheets by Microsoft Forms.
Highlight all the columns with numbers and use the 'clear formatting' option in the 'Home' set of commands for your sheet... that will remove all the apostrophes... it was driving me nuts with other solutions being way way more complicated.
A picture from the original post linked above is helpful for finding this option...
Edit
Possible solution for continuously arriving data into, for example, cell A2 ....
=VALUE(RIGHT(A2,LEN(A2)))... this formula will give a new cell with the apostrophe removed and the string converted to a number.
in fact VALUE() does the job on its own...
