yogi_Row By Row Average Of Numbers In Columns

Google Spreadsheet   Post  #2166

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-23-2017

question by zr2ee:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/wPvKrotAaAc;context-place=mydiscussions

averaging form submission data and optimizing formula's
Ultimately this is probably going to be a long thread as i'm fairly new to advanced formula's and have multiple issues i need to work through however for the sake of simplicity i'm going to try to focus on one thing at a time.

I have 3 forms submitting raw data to a workbook, i have several additional worksheets that i'm using to merge and do math on the raw data (mainly averaging), What is the best option for averaging raw form data from form submissions?

• Currently i've tried the =iferror(AVERAGE('Step 1 (form)'!J2:N2),)
• and =ArrayFormula(if(ISBLANK('Step 1 (form)'!E2:E)," ",'Step 1 (form)'!E2:E+'Step 1 (form)'!F2:F+'Step 1 (form)'!G2:G+'Step 1 (form)'!H2:H+'Step 1 (form)'!I2:I)/5)

i like the array formula but i'd like to have it ignore anything that is blank or zero but so far all the suggestions i've seen haven't worked for my formula.

Here is a Sandbox version of the workbook, feel free to comment and edit: Sandbox workbook

moving forward i will be looking to align and batch the raw data together hopefully automatically