Wednesday, April 17, 2013

yogi_Create A Computed Column In A FormResponses Sheet For Row By Row Score Ignoring NA Responses

                                          Google Spreadsheet   Post  #1138
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 17, 2013
user doc bodkin :(http://productforums.google.com/forum/?zx=m2hy26mxofcx#!mydiscussions/docs/_-ipdGPP3Qc)
How to get a form submission into the next row on the spreadsheet without adding a new row
I have a form that gets several numerical answers submitted. On the responses spreadsheet I set up an extra column that has a formula in to do a simple calculation based on those answers. However, when a user submits a form, it just generates a new row into the spreadsheet after the last response. How do I get it to just populate the next available row so that I don't have to keep dragging my formula down to the new row to get my calculation?

I am using Firfox on a Windows Vista machine, but ideally would like this to work on my iPad.

---
Hey Yogi,
Here is the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0As0cL09iJbGadFdKMXFSSEdSejQyeVIxVDJNaXlJaEE&usp=sharing

Column P contains the actual formula
ignore Column Q
Then Column R is my attempt at an ARRAYFORMULA. I had it without the IF section and it couldn't understand cells that had "NA" in them but otherwise worked beautifully. I added the IF function and now my parsing is off and I am not sure how to fix it. 

Thanks,
Brad

-----------------------------------------------------------------------------------------------------------------
following is a solution to the problem