Saturday, June 20, 2015

yogi_Compute For Data In Several Columns Row By Row Average Of Numbers And Associated Percentage


                                           Google Spreadsheet   Post  #1968
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jun 20, 2015
question  by AlexIR:
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!category-topic/docs/spreadsheets/Ywg92-ccxRs
Stop Forms from shifting rows in Sheets
Hi everyone,

I have a small survey in Forms which asks for the participient's number of Facebook friends and the likes for his 5 recent profile pictures. Thus the colums in the Response Sheet looks like this:

Form Responses 1:
--------------------------------
A: Timestamp
B: # Friends
C: Likes Picture #1
...
G: Likes Picture #5
--------------------------------

For the calculations I have another sheet with three columns:

Calculation:
--------------------------------
A: #Friends: ='Form Responses 1'!B2
B: Average Likes: =AVERAGE('Form Responses 1'!C2:G2)
C: Percent: =(B2/A2)
--------------------------------

I wrote the Formulas into Row 2 and then copy-pasted them over all rows below.

Now here is the catch:
As soon as someone makes a new entry to the form, the respose inserts a new row to the Responses sheet, which also creates a gap in the calculations: 

Calculations:
--------------------------------
A2 = Form Responses 1'!B2
A3 = Form Responses 1'!B4--------------------------------

How do I stop this from happening?

Thanks in advance,

Alex

---------------------------------------------------------------------------------------------------------------