yogi_MultiConditional Count Of Unique Advocates With Engagement Level Of 3 By Legislator

                                          Google Spreadsheet   Post  #1459
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-31, 2013
question by Meredith Poynter (http://productforums.google.com/forum/?zx=76qbjtbjhn60#!category-topic/docs/spreadsheets/6QdasYI2-SE)
How to make this SUMPRODUCT (or COUNTIFS) formula to work in Google Spreadsheets?
 The original table I pasted was a very basic and generic form of what I'm working on. This is the real thing (with identifying info changed or deleted)

https://docs.google.com/spreadsheet/ccc?key=0ApOYbVKFbSMNdFVGN0JIdmhIRVRPQTZfQ043WndtS3c&usp=sharing

The data table is the "Form Responses 2" tab as the info feeds in from a form.

I'm looking for a formula to put in the cells 'Advocacy Engagement'!P3:P32. The results of the formula should display the number of advocates that have an engagement level of "3" ('Form Responses 2'!I2:I105) for the legislator that same row. This must be done without counting duplicate entries of the same Advocate by Legislator ('Form Responses 2'!D2:G105). In other words, these advocates could be entered multiple times ex. Bob, CEO of  K Bank may be entered on the form 2 or more times. This might be because he is advocating with more than one legislator, or he is entered in mulptiple times because his engagement level went up with a particular legislator, or simply multiple notes associated to him has been entered. I only want him to be counted once for that legislator in that row and only if he has reached a level 3 engagement level. The result should be a count of the number of advocates that meet this criteria for that legislator. 

As an aide to the formulas on the "Advocacy Engagement" tab, I used column A to show the full name of the Legislator so it matches the legislator name in 'Form Responses 2'!D2:D105.

Also, know that this can not be a count of the "3"s in 'Advocacy Engagement'!J3:O32 as those formulas show the highest level of engagement for that Advocacy Type (column header) regardless how many advocates there are. 

I don't know of any other links to the same or similar question like mine. I searched before I posted my question.

Hopefully this answers your questions and you can help me with this. Please let me know if you have any other questions.

Thanks in advance!

-------------------------------------------------------------------------------------------------------

yogi_Compute Key Business Metric Total Active Students In Last 30 Days Directly From Data In FormResponses Sheet

                                          Google Spreadsheet   Post  #1458
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-29, 2013
question by Universal Account (http://productforums.google.com/forum/?zx=5j3c3wqmicu2#!mydiscussions/docs/g0NE6a0Mtcw)
How to combine filter and google clock in a way that works?

Why does the following not work?

=COUNTA(FILTER(CK:CK;CK:CK="Open", A:A>=datetime '"&text(GoogleClock()-30,"yyyy-MM-dd HH:mm:ss")&"'))

I know I can get a rolling 30-day view of data using query and google clock in this manner. Can I not use it in this format for filtering data? Related, how do I intelligently filter by month only - also based on Google Clock. For instance, always show 1 month back in relation to current time? I see many examples for calculating static date ranges in this forum; I have not encountered any using filter.

Appreciate any help immensely!

-Jonathan

---

Example spreadsheet: 

https://docs.google.com/spreadsheet/ccc?key=0AorgbSDvY2oTdHg5bVlaYXl2Q3lxNE9sWVVqcXlXMmc&usp=sharing

FormResponses comes from a Google Form. It contains Enrollment Info for a school. Imagine it has 500 entries, with dates spread across months and years.

------------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull Header (From Row 1) Where Multiple Criteria Intersect

                                          Google Spreadsheet   Post  #1457
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-28, 2013
question by R.Todd (http://productforums.google.com/forum/?zx=fsgl3dns483z#!category-topic/docs/spreadsheets/kG8WxBkElh8)
Return Column Header w/ Multi Criteria

I'm trying to find a way to return the column header for a row matching multiple criteria in Google Spreadsheet. I've been able to successfully use INDEX MATCH to return the header for a single variable, but when I add in the second one, I can't find how to get it to return the column header for the second variable only.

It's easier to see if you look at it here: https://docs.google.com/spreadsheet/ccc?key=0AudCiUHutgYhdGZlRFZ2RGE2N1pfajloUk51NE9oaEE&usp=sharing

In the mockup included above, A2:A4 contains criteria 1, while B2:F4 contains criteria 2. I need to return the column header(s) for the row that meets both criteria at the intersection(s) of the column(s) crossing criteria 2. I've tried various attempts with INDEX MATCH and QUERY (of which I'm not great) and haven't been able to get anything. My preference is to keep this to FORMULAE, but any input would help.

Any thoughts? Eventually the final dataset will include multiple worksheets using indirect references, but I just need to get this first component functional

---------------------------------------------------------------------------------------------------------------

yogi_Pull Phrases (Sentences) From Column A That Contains Words or Phrases In Cells B2 to B (open-ended range)

                                          Google Spreadsheet   Post  #1456
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-28, 2013
question by sailingonsound (http://productforums.google.com/forum/?zx=l86ee95dkrjv#!category-topic/docs/spreadsheets/xpqHOI1JGIQ)

Searching a column against a column to generate a column

I think this is pushing right to the very edge of what is possible to do in google docs (without scripts), I'm about to write code to do this but thought it might present a great ultimate challenge for the formula pros :)

A1: Title

A2: Snow White and the Seven Dwarves

A3: Beauty and the Beast

A4: Snowey snowtrack the snowmobile

A5: Snow Beasts and Dwarves

A6: A Snowy Christmas

B1: Find Words

B2: Snow

B3: And

C1: Result Sentences

C2: Snow White and the Seven Dwarves

C3: Beauty and the Beast

C4: Snow Beasts and Dwarves

So essentially the function would find all the sentences in Col A that contain any of the words in Col B and spit them out in Col C. It finds sentences that contains words for kids learning english, so if we know a kid is struggling with a few words we can find really good sentences to challenge them with. 

I was thinking that before doing a comparison you could add a " " to the end of the words, then add a " " to the end of the Sentence Col as well so it doesn't misfire on "Snowy" and the like but the array function feature is mystifying me. The closest I got was this

=SORT(UNIQUE(FILTER(B2:B;COUNTIF(UPPER(A2:A);UPPER(B2:B))>0)))

But it can only compare words to words, it'd be like that but with a search/find feature.

----------------------------------------------------------------------------------------------------------------------------------------

yogi_Set Up Table And Calc For Multiple Repeats Of Specified Percentage Of Weight for 1 rep max

                                          Google Spreadsheet   Post  #1455
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-28, 2013
question by doublezebra (http://productforums.google.com/forum/?zx=ym0xgc9fgk0f#!category-topic/docs/spreadsheets/zsy8AoYHy_M)
How to set a variable in formulas
'm setting up a spreadsheet to track my weightlifting progression. For certain lifts, I have to calculate my 1-rep max and then do reps based as a percentage of that.  For example:

1 rep max = 200
75% 1RMx5 = 5 reps of 150
80% 1RMx5 = 5 reps of 160
85% 1RMx5 = 5 reps of 170

Every week, the value of the 1RM variable will go up. It'd be nice to be able to simply set a variable so that x=200 so that next week all I have to do is change the variable so that x=205 or x=210 so I don't have to edit each cell manually. Is there a way to do this?
-------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Pull Phrases (Sentences) From Column A That Contains Words In Cells B2 And/Or B3

                                          Google Spreadsheet   Post  #1454
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-28, 2013
question by sailingonsound (http://productforums.google.com/forum/?zx=l86ee95dkrjv#!category-topic/docs/spreadsheets/xpqHOI1JGIQ)
Searching a column against a column to generate a column

I think this is pushing right to the very edge of what is possible to do in google docs (without scripts), I'm about to write code to do this but thought it might present a great ultimate challenge for the formula pros :)

A1: Title

A2: Snow White and the Seven Dwarves

A3: Beauty and the Beast

A4: Snowey snowtrack the snowmobile

A5: Snow Beasts and Dwarves

A6: A Snowy Christmas

B1: Find Words

B2: Snow

B3: And

C1: Result Sentences

C2: Snow White and the Seven Dwarves

C3: Beauty and the Beast

C4: Snow Beasts and Dwarves

So essentially the function would find all the sentences in Col A that contain any of the words in Col B and spit them out in Col C. It finds sentences that contains words for kids learning english, so if we know a kid is struggling with a few words we can find really good sentences to challenge them with. 

I was thinking that before doing a comparison you could add a " " to the end of the words, then add a " " to the end of the Sentence Col as well so it doesn't misfire on "Snowy" and the like but the array function feature is mystifying me. The closest I got was this

=SORT(UNIQUE(FILTER(B2:B;COUNTIF(UPPER(A2:A);UPPER(B2:B))>0)))

But it can only compare words to words, it'd be like that but with a search/find feature.

---------------------------------------------------------------------------------------------------------------------------------------------

yogi_Highlight Names In Column A With Red Background If Name Is The Same As In Cell B1

                                          Google Spreadsheet   Post  #1453
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-28, 2013
question by Mladen Jurak (http://productforums.google.com/forum/?zx=l86ee95dkrjv#!category-topic/docs/spreadsheets/778zOTWtK9w)
Oblikovanje polja u zavisnosti od vrijednosti druge ćelije (Forming field depending on the value of another cell)

Hello,

how can I conditionally format a field such as A1 compared to B1.

If the value is different from A1 B1, A1 field I want to paint red.

Thank you.

Mladen

-------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Set Up Formula Using INDIRECT Function To Be Dragged Down

                                          Google Spreadsheet   Post  #1452
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-27, 2013
question by rand2002 (http://productforums.google.com/forum/?zx=wke6o1qgwz6q#!category-topic/docs/spreadsheets/z9nczi618hQ)
Dragging the =INDIRECT function down rows
https://docs.google.com/spreadsheet/ccc?key=0Avk95-mFPEf4dFRnSjc4eDVIV1pYT1FUOWVvakFqSlE&usp=sharing

In the above sheet, I had a simple function in column C that compared the changes between column D & E.   it was  =(D2-E2)/D2

Each day I add a column left of D and insert today's data.  Afterwards, I have to adjust column C to find the percentage change between column D & E again (because it adjusted D2 & E2 to E2 & F2 when i add a column left of D).

So today I found the INDIRECT function that wont adjust to E & F when I add a column left of D each day.  It works in the first Row when I add quotes around the cell reference, but I cant copy it down to the next row. 

by the way I cropped the sheet to only a few rows, the original has over 550 rows.

Got to be a short cut right?  Please and Thanks for the help.

-----------------------------------------------------------------------------------------------------------------------------------------

yogi_For An Entry In Cell A1 LookUp Value In Column 2 Of Virtual Table Made From Table Of Data By User

                                          Google Spreadsheet   Post  #1451
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-27, 2013
question by Jennifer Travlos (http://productforums.google.com/forum/?zx=wh33mpxca23w#!category-topic/docs/spreadsheets/tSAHJBd7eH0)
Multiple If Then's with multiple conditions

Hi all. I understand the basic concept of an "if then" statement. I'm having an issue with multiple conditions. I have column A that states any number of 1 through 21. In column B I want it to automatically write a percentage:

21 = .1

20 = .09

19 = .08

18 = .07

17 = .06

16 = .05

15 = .04

14 and under = 0

So if A says 21 B would say .1. If A says 13 B would say 0. If A says 18 B would say .07.

Does anyone have an idea how to do this? Hopefully I am writing this clearly.

-------------------------------------------------------------------------------------------------------------------------------------

yogi_Return Last 3 Unique Entries From Column A Of Raw_Data And Corresponding Values In # Adjacent Columns To The Right

                                          Google Spreadsheet   Post  #1450
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-26, 2013
question by Jaxiu (http://productforums.google.com/forum/?zx=wh33mpxca23w#!category-topic/docs/spreadsheets/wibFsP5pUw0)
Returning last populated cell and rows adjacent to it, and more...
I have a sheet called "raw_data"
- it has 4 columns with data in them
- only column A always has data in it

I've been using this code to return the last populated cell from row A:

=INDEX(FILTER('raw_data'!A:A;NOT(ISBLANK('raw_data'!A:A)));ROWS(FILTER('raw_data'!A:A;NOT(ISBLANK('raw_data'!A:A)))))

it works like a charm, but I also need data from other columns from the same row,
and I can't use the same code by simply changing the "A:A" because cells in row B, C and D can be null

That's my first problem, the second one is:
I need to return a populated cell before the last one BUT it has to be different than the one that I get from the code above, and also the rows adjacent to it.

I'm sorry if this has been posted before, I tried figuring it out on my own, but I just hit a wall.
Thanks in advance for any help.
---
There you go:

http://docs.google.com/spreadsheet/ccc?key=0AoOeJJd-r16wdDkyY055TG5vOXNMZmN4SmRKQUh5R2c&usp=sharing
-----------------------------------------------------------------------------------------

yogi_Compute Address(es) Of Maximum Values In A Column And Then Offset From Maximum Values To Find Best Teams

                                          Google Spreadsheet   Post  #1449
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-26, 2013
question by Paul Kioko (http://productforums.google.com/forum/?zx=wh33mpxca23w#!category-topic/docs/spreadsheets/yZ_6v5HbWtc)
Finding the Row of a max value

I am trying to get an address(cell name) of the max value in a range. So what i have is

=Address(Row(Max(S1:S5)), Column(Max(S1:S5)))

After that i want to do a offset for a different value, but i can't get passed the one above as it says argument must be a range

---

Here is a copy of the spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0AlTJqZKyRi55dFF0MHRnSlo1bk9YaGpHN3N3RFVYUWc&usp=sharing

-----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Use QUERY Function Using Named Ranges For Source Data And Field Name(s)

                                          Google Spreadsheet   Post  #1448
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-26, 2013
question by Isai Alvarado (http://productforums.google.com/forum/?zx=e80jmfn2nptg#!category-topic/docs/spreadsheets/urtd8hGGOvk)
Query formulas with cell references HELP
Hi,

If I type =query(PTS," "&E8&" ") 

and E8 has select A,B,C where (A='P' and B<8 and C>=2), the formula works.

However, if E8 has a named range select A,B,C where (A='P' and B<"&csp&" and C>=2), it doesn't recognize the named range.

Is there a way to make the query function recognize the named range csp that is inside E8?

Example spreadsheet: https://docs.google.com/spreadsheets/d/1k_kVqHjI_6oAqARq3zgieMpanfwTZJJIJCfxUqhoxxE/edit#gid=0
-------------------------------------------------------------------------------------------------

yogi_Extract Partial String (ending at .com) From Cells Of One Column To Cells Of Another Columns

                                          Google Spreadsheet   Post  #1447
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-26, 2013
question by Cassidy Price (http://productforums.google.com/forum/?zx=e80jmfn2nptg#!category-topic/docs/spreadsheets/OKEPzLavo8M)
Extract partial string from one cell to another

I want to extract the domain from a list of urls from one column to another. For example;

I want to take this url from column A, e.g.

http://www.philly.com/philly/health/topics/HealthDay683371_20131226_White_House_Extends_Insurance_Enrollment_Deadline_for_Second_Time.html

and extract only the domain name to column B, e.g

http://www.philly.com

So for example, if I have a list of urls in column A:

http://www.philly.com/philly/health/topics/HealthDay683371_20131226_White_House_Extends_Insurance_Enrollment_Deadline_for_Second_Time.html

http://www.cnn.com/2013/12/26/showbiz/doctor-who-christmas-matt-smith-farewell/

http://abcnews.go.com/US/wireStory/friday-power-restored-areas-21337106

I would end up with a list of domains in column B:

www.philly.com

www.cnn.com

abcnews.go.com

Can this be done? I've been trying to figure out a way to use regular expressions to accomplish this but I'm stumped. Thanks for any help!

--------------------------------------------------------------------------------------------------------------------------------------------

yogi_MultiCell Array Formula For Computing Row By Row Minimum For Entries In Range B5 To H8

                                          Google Spreadsheet   Post  #1446
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-25, 2013
question by QusdT (http://productforums.google.com/forum/?zx=kfloo0schu1p#!category-topic/docs/spreadsheets/83KuHWqzeAQ)
ArrayFormula not continueing - AKA Using the MIN FUNCTION with ARRAYFORMULA
The function I'm trying to perform in an arrayformula is:

=ArrayFormula(IF(COUNT(B5:H5)=7,ROUND(MIN(B5:H5),2),""))

I've seen some alternative solutions instead of using MAX posted by Adam & Yogi with explanations that MAX doesn't work so well in ArrayFormula; so I assume since MIN & MAX are best friends that MIN doesn't get along well with ARRAYFORMULA either.

Although just having the correct formula would be AWESOME. It sure would be nice to have the sections of the formula explained so that I can figure it out on my own for future needs.

Thanks Everyone...I sure do appreciate this forum, I use it regularly!

-------------------------------------------------------------------------------------------------------------------------------------------------