yogi_Pull Into Another Column Text Left Of Material Within Parentheses

Google Spreadsheet   Post  #2172

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

question by DaveLLL:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/7UbAXBhHXW4;context-place=forum/docs
TRIM(MID(SUBSTITUTE) - Return value in between two strings

Hi all,

I want to return the value in-between the parentheses in a cell. For example:

A1: Nicky Ring (Grow Learning)

A2: Chloe Fulman (Jeanswest)

A3: Corey Brewer (Uphill)

A4: Charles Post (Skilled People)

A5: David Chow (Easy Dreams)

 
So output would be:
B1: Grow Learning
B2: Jeanswest 
B3: Uphill
B4: Skilled People
B5: Easy Dreams

Your help is much appreciated!

yogi_From dropDown List of Company Names Create dropDown List Of Persons And Then Pull Person Phone Number

Google Spreadsheet   Post  #2171

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

question by BradyL:

https://productforums.google.com/forum/#!topic/docs/M4vJ8SkOLZA;context-place=mydiscussions

Auto-Input Names & Phone Numbers

I am trying to figure out how to auto-input phone numbers to a neighboring cell after a DDL selection. I have orders that I assign different companies to handle. Within each company, are several different employees. Each employee have their own cell phone number. What I am looking to do is, select and company from a DDL, then to the cell to the right have another DDL appear with the employee names of that specific company. Then after choosing which employee, I want their phone number to be automatically placed in the cell to the right. If anyone can help me, it would be greatly appreciated!

yogi_Adjusting For Time In hh:mm:ss As Duration Using QUERY Function Versus Using FILTER Function

Google Spreadsheet   Post  #2170

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

question by incolas:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/9jdwt0DDqlk;context-place=mydiscussions
When I use query on a duration, it shrinks the duration
Let's say the duration is 70 hours in the original data, when I query, I get 22 hours (which is 70-48).

And this is just one example; all queried durations get shrunk in the same way.

I unfortunately can't share the spreadsheet but I've seen very similar issues mentionned several times in the forums here, without answers...

Please help!

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

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

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

Please see my sheet: https://docs.google.com/spreadsheets/d/1b8SaycW_sMGvbOUkBlfRhO5AoA46ewvTnztnjgZXN98/edit?usp=drivesdk

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:

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

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).

https://docs.google.com/spreadsheets/d/13fLEQKF92e3MehyEt1SA_hQRl-c_4HQQUU24Oqc3KS4/edit#gid=0&fvid=688414131

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:

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

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:

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

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

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:

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

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.

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:

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

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

yogi_Compute Column By Column MIN MAX And Corresponding Month For Each Item

Google Spreadsheet   Post  #2162

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

question by jacquespar:

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

Find matching date for min and max value
I want to find the month that matches the min and max values, as per the example:

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

yogi_Pull Row By Row GroupNo For Which Entry in That Row Is The Largest Entry In That Row

Google Spreadsheet   Post  #2161

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

question by Hồ Quang Hướng:

https://productforums.google.com/forum/#!topic/docs/UFZB8NZBMAE;context-place=forum/docs

SUMIFS providing correct values only when I delete&paste the same value

I have used an index/match/max filter to identify column headers of cells with a value greater than zero in a given range. This works fine when there is only one such cell in a range. But what if there are multiple cells with a value greater than zero in a range? Is there a formula I can use to identify all column headers for all cells with values greater than zero in a given range?

yogi_Multi Criteria Summing From Filtered Data

Google Spreadsheet   Post  #2160

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

question by maddy:

https://productforums.google.com/forum/#!topic/docs/GSYwdTbjcqw;context-place=mydiscussions
Identify column headers for cells that meet criteria in a range
OK.. cant  figure out why.

for the first condition if I delete and paste the same value i.e., 2 in D1, it returns correct value.

Shouldnt it by default take 2 the first  time itself.

SHEET

**you can take a copy of the tab to test

yogi_Pull Row By Row GroupNos For Which Entries Are Greater Than 0

Google Spreadsheet   Post  #2159

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

question by CAB05:

https://productforums.google.com/forum/#!topic/docs/UFZB8NZBMAE;context-place=forum/docs
SUMIFS providing correct values only when I delete&paste the same value

I have used an index/match/max filter to identify column headers of cells with a value greater than zero in a given range. This works fine when there is only one such cell in a range. But what if there are multiple cells with a value greater than zero in a range? Is there a formula I can use to identify all column headers for all cells with values greater than zero in a given range?

yogi_Flag With 'S' Row By Row If Time In Column B Plus Duration In Column C Is In Range Of Clock Time In Row 1

Google Spreadsheet   Post  #2158

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

question by MatKa!#:

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

Timesheet creation

 Hello

Im trying to create a timesheet and i need to mark the hours needed for each task with S

So i have the following columns : Name, Task, Time Start and Duration... What i need is, if Time Start is 10:00 and the duration is 3 to feel in with S the hours 10:00 - 13:00

Here is my example sheet for explanation

https://docs.google.com/spreadsheets/d/1dD1ZbSDhprviJ1sO-mtUopOuq-XCyAXD9kzel-GN_Do/edit?usp=sharing

Any help will be appreciated 

Thanks.

yogi_Compute Weekly Stats From Hourly And Daily Data

Google Spreadsheet   Post  #2157

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

question by KM1388:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/MA6PvQQSGLU;context-place=mydiscussions
Need help on sum and count formula please.
Hi all,

I need some help on sum and count formula for a simple calculation.

=Query(Hours!A:G,"Select A,B, MAX(C),SUM(E),SUM(F), 3*(COUNT(D)+COUNT(F))+sum(G) where A is not null group by A,B",1)

on the formula i use 3*(COUNT(D)+COUNT(F))+sum(G) the result came out null value, if the column G value is null value. you could compare the output on column F and column H with the share link below:

can anyone help please!!!

https://docs.google.com/spreadsheets/d/1ySwvoKx-8WOkc09DGckpaWE_9D_Rp3OPpYzmbKLK0Zw/edit#gid=669098519

Kenneth