yogi_Combine Several Sparkline Charts And Headers In An Array Formula (brute force method)

Google Spreadsheet   Post  #2169

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-25-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
i was able to transpose the formula's across the remaining columns that i'm averaging and am working to better understand the query functions, it's definitely powerful i just don't have a good grasp on it at this time.

Question number 2.

Is there a way to create a array type function with sparkline graph's

currently i'm using a formula in each cell of a column like this: =iferror(SPARKLINE('Step 3 (form)'!L2:R2,{"charttype","line"}))

but if possible i'd like to narrow it down to one header cell formula like the previous solution. 

I have hidden the sheets and columns unrelated to the question

1) please give us one Sheet (for the source data) ... don't give us the whole project

Source data sheet: (step 3 (form))

2) give us one sheet for the output

Output sheet: (Product 2 Validation)

4) just tell us what you are trying to accomplish

I'd like to use a single formula to create a set of sparkline graphs down a column of cells

5) tell us what is your expected result ... in which cell? of which sheet?

sparkline graph's down 'Product 2 validation'!H3:H comprised of data from 'Step 3 (form)'!L:R

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