Thursday, May 25, 2017

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:
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_Create Logs For Items And Dates Based On Table In Rates Sheet

Google Spreadsheet   Post  #2168
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-25-2017
question by MiVoJa:
What's the best array formula for converting different currency?
Hi,
I'm trying to convert different currencies. What is the best formula if I'd like to place it on the top cell. I'm thinking of placing it on Rate tab - D1

Tuesday, May 23, 2017

yogi_Facilitate Filtering Data In FilterView1 based On Variables Specified In Cells A2 to A6 In Sheet2

Google Spreadsheet   Post  #2167
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-23-2017
question by schnikket79:
filter table using multiple keywords that are all located in one column
Here is a sheet I made to help illustrate my problem (it's a reduction of a much larger table).


My large table lists details about various musical works and one of the columns lists the instruments (keywords) that are needed to perform those works. 
(In the example sheet, keyword column is column I).

I don't want to change the layout of this table at all (it is used by my other sheets that rely on that particular sort, with all the rows unfiltered).

I want to be able to:
- go into this sheet's Filter View (e.g. Filter1 in my example sheet)
- choose my instruments (i.e. keywords), e.g. type them into the orange cells on Sheet2
- get a filtered table where my Sheet2 choices retrieve matching rows. 

Example:
I have there right now Sheet2!A2 == "Piano" and Sheet2!A3 == "Voice"
I want all rows where column I cell contains those two keywords to be shown.
This would include entries such as "Piano, Voice", "Piano, Voice,", or "Piano, Voice, Clarinet in Bb,". 
*It would not include entries such as "Piano Solo", for example, because my Sheet2 choice of Piano and Voice requires that both of those keywords be present within one cell.

*To limit my filter to only the rows with Piano, Voice and nothing else, I was thinking of using some kind of "terminator". Say, in Sheet2!A4, I'd put "XXXX".
EDIT: I can imagine "terminator" being a problem. I have a way of working around this issue. So, this doesn't need to be addressed.

I am guessing the above is possible using some clever Filter by Condition custom formula (including some regex stuff), but everything I have tried has failed me so far.)

I would love any help!


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:
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

Friday, May 19, 2017

yogi_Look For Student Name In Several Columns And Mark YES If The Student Has Taken The Quiz

Google Spreadsheet   Post  #2165
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-19-2017
question by KathleenWatson4s:
Looking to search several columns with array formula
I am hoping I can find someone to help!

I have a google form quiz and it asks what students are in which classrooms as different questions. They end up in spreadsheet in different columns. I am trying to figure out a way to use the search function to pick up their names in all the possible columns their name could appear in

Here is a mock spreadsheet mirroring what I am trying to do!

https://docs.google.com/spreadsheets/d/1EldzwHBfpsOHL0PgPkL9oM_hsb0kayUaLUfzR9Hg6kQ/edit?usp=sharing

Thursday, May 18, 2017

yogi_For Specified Words Pull Unique Values For FIELD1 And FIELD2 And Present As Comma Separated Values

Google Spreadsheet   Post  #2164
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-18-2017
question by ai tis:
Query Combining duplicate matches while excluding copies
I've been using the following function:

    =query(Sheet1!A2:D," select A, B, C where A matches '"&JOIN("|", A2:A)&"' and D matches 'yes'")

This function returns 3 rows with the same string in column A along with some other rows which have different strings in column A.  For the case where column A has the same string for various rows, I want to combine the columns selected (a, b, c) into new columns a, b, and c.
In other words, I want every row that starts with a match will be added a comma separated list in which each column occupies one cell with no duplicates as shown in sheet3.

https://docs.google.com/spreadsheets/d/1YDxIUnZzzYde9hcexPoDegv4HBuiUwk2wLKSXazu9hE/edit?usp=sharing

Sheet 2 has the function that I used and the result.

Wednesday, May 17, 2017

yogi_Array Formula of SUMIFS with partial cell text match not expanding

Google Spreadsheet   Post  #2163
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May-17-2017
question by Clayton Clixo:

Array Formula of SUMIFS with partial cell text match not expanding

I'm having trouble with an arrayformula that won't expand. It's in cells B4 and C4 on the summary tab. I know the base sumifs formula works and if I copy down the array formula to lower cells it works, but it won't expand by itself. Any help is much appreciated!

Maybe it's not running because:
 - using sumif that is looking up a partial text string within a cell?
 - referencing a cell value that's populated by another array formula (column A on summary tab)

Sample spreadsheet