Saturday, July 7, 2012

yogi_For Maximum Possible Specified Score Compute Percent Score Per Row

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #612   Jul 7, 2012     www.energyefficientbuild.com.


user ers78 said:
Array formula issue...having hard time scripting what should be a simple operation
I have a spreadsheet that is accepting data from a Google Form, and I want to have a column that calculates an average of the numbers in each new row that gets added.  The form is a survey where each response has a value of 1-7.  The average score column (column C) is going to be scaled from 1-100.  Here's what my formula wants to be:  SUM(D2:U2)/(COUNT(D2:U2)*7)*100 , and each time a new row is populated by the Form, have the formula continued down.
I keep thinking it should be an array formula, but I'm not having any success getting it to apply to each incoming row of data.
Thanks!
------
Here is a link to a dummy spreadsheet, which is associated with a Google Form.  What I want to happen is for the formula in column E to continue down the column as new data gets added via the Form.
https://docs.google.com/spreadsheet/ccc?key=0Al6Mqo6yvd9WdGtJVzJUeGVtVGg4OTVxaWhndVRwLVE
Many thanks
-------------------------------------------------------------------------------------
projecting form 1 row data by the user, I have posted the solution to the problem as follows