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    Jun 20, 2015
question  by AlexIR:!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:

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: 

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

How do I stop this from happening?

Thanks in advance,