yogi_LookUp The Value Of An Entity From A Given Table Of Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user superfast502 said:
Write a conditional if statement where a value can fall within 6 different ranges?
If I have a range of six values (a1,a2,a3,a4,a5,a6), and the distance between say a1 and a2 is 'range1', a2 and a3 is 'range2', and a3 to a4 is 'range3', etc. The final number (a8) how do I write a function that would tell me which range the final number fell into?
For example:
a1 = 118
a2 = 123
a3 = 129
a4 = 132
a5 = 135
a6 = 140
a7 = 146
Between 118 and 123 is range1
Between 123 and 129 is range2
Between 129 and 132 is range3
Between 132 and 135 is range4
Between 135 and 140 is range5
and finally between 140 and 146 is range6.
I have a final value, say 124. How do I get the spreadsheet to display "range2"
or, if the final value is say 133 the spreadsheet would display "range4"
Sure appreciate any help I can get.
Thank you.
---------------------------------------------------


following is solution to the problem:

yogi_Count Number Of Records Within A Column And Present As A Table

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user drewski said:
Have a column with numbers ranging from 1-10 which refer to what step we are in for a particular process...
I simply want to have a table that looks something like below. 
STEP      # of Steps
1             9
2            22
3            18
....
10            4
Just want to do some basic matrices showing how many records are in a particular step 
And help would be greatly appreciated!
--------------------------------------------------

yogi_Pull Data For Certain Actions By Month Using Info From Timestamp

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user FroSho21 said:
I need to figure out how to pull data for certain actions for each month, using the time stamps?

Timestamp           Time Spent on Procedure    Reason for Visit
2/20/2011 19:00:45               30            Heliox with High Flow Nasal Cannula   

2/10/2011 19:00:45               35            Heliox

2/8/2011  19:00:45               90            Intubation

2/8/2011  19:00:45               70            Level 1 Trauma

10/18/2010 2:00:45               15            Home Care Vent/Trach Patient

An additional question would be is there a way I can count the total number of each procedure per month per year?So I am trying to pull monthly totals of time for each type of procedure but I also need to divide it between the years 2010, 2011, etc....

Thank you!Thank you all for your help, you have been wonderful....my transition to utilizing google docs and google forms has been great thanks to help from individuals such as yourselves.
--------------------------------------------------------------

following is one solution to the problem

yogi_Compare Two Columns In A Sheet With Two Other Columns And Mark As 'yes' Or 'no'

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
use lakshmid said:
How to compare two columns in excel sheet with other two columns and mark them as "yes" or "no"
I want formula to do this below scenario

There are two lists.
List 1 = Column A + Column B + Column C+ Column D
List 2 = Column F + Column G

List 1

Orange, Cara Cara , 1, 2
Orange, Navel     , 3, 4

Orange, Blood     , 7, 8
List 2

Orange, Cara Cara
Orange, Blood

Now, I want columns A &B to be compared to F &G and post "yes" of the value is present in list 2 and "no" if its not present in column D.

Output

Orange, Cara Cara , 1, 2  yes
Orange, Navel     , 3, 4   no

Orange, Blood     , 7, 8  yes

Please let me know ASAP.

Thanks

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

following is one solution to the problem

yogi_Convert Column Of Email Addresses To Hyperlinks

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user BrighterLater said:
Convert column of email addresses to hyperlinks
Is there an easy way to convert a text email address in a cell to a hyperlink? I have 100 of them and do not want to have to edit each on in turn to add a hyper link function and "emailto:"
------------------------------------------------------------
following is solution to the problem:

yogi_Make The Column Headers And Answers Correspond After Changing Order of The Answers In Form Responses Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
This has been an issue with Google Forms and there was a recent discussion on this in the following thread:
Need to change order of form responses in linked spreadsheet 
As I had stated in that thread and I will check out whether my proposed solutions work ... so when I started checking it out today ... I was pleasantly surprised -- there is no need for a fix ... when the order of the question in the Form Responses is changed the Form Responses are logged in correctly in the right columns ... Bingo!
This doesn't mean that all situations ... all eventualities have been taken care of ... but this part as we know of is fixed -- a huge THANKS to Google Engineers for this!
-------------------------------------------
here is the Form ... please note the Form had been EDITed and the questions were rearranged so that the order of the questions does not correspond with those of the column headers in Sheet1 (the so called Form Responses sheet) ... so in essence, even though questions in the Form can be rearranged, and even the answers columns in Sheet1 can be rearranged independent of each other, the responses from Form submittals are correctly logged in appropriate columns.



and here is the Form Responses sheet ... please note that the Answer columns have been rearranged and the order of the Answers columns is not the same as the order of the Question columns in Google Form. However when a Form is submitted the entries are logged in the right column ... Bingo!

yogi_Make The Column Headers And Answers Correspond After Changing Order of The Questions In Google Form

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
This has been an issue with Google Forms and there was a recent discussion on this in the following thread:
Need to change order of form responses in linked spreadsheet 
As I had stated in that thread and I will check out whether my proposed solutions work ... so when I started checking it out today ... I was pleasantly surprised -- there is no need for a fix ... when the order of the question in the Form is changed the Form Responses are logged in correctly in the right columns ... Bingo!
This doesn't mean that all situations ... all eventualities have been taken care of ... but this part as we know of is fixed -- a huge THANKS to Google Engineers for this!
-------------------------------------------
here is the Form ... please note the Form had been EDITed and the questions were rearranged so that the order of the questions does not correspond with the order of the questions in the Form Responses sheet

and here is the Form Responses sheet ... however, please note that even though the column headers in Sheet1 do not correspond with the way the questions are ordered in the Form, the answers from Form submittals are logged in the appropriate columns.

yogi_Extract Data For Repetitive Entities By Latest Date

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user jamiewig said:
Query on date cleaned
I have a list of window cleaning jobs and need to run a query on the  the last time each one was cleaned (date) and then  work out the next clean date which would be 4 weeks from that date. can any one help
-----------------------------------------------------

see the following for solution to the problem ...

yogi_Read An Attribute Of An Entity Where Only Part Of It Matches Tabular Value

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user DoctorBean said:
Use VLOOKUP if the reference matches only a part of the search criterion
I'm trying to use VLOOKUP to return a value if a part of the input cell matches the reference table.
I've made an example spreadsheet:
A1: pears
A2: apples
A3: oranges
A4: grapes
B1: 1
B2: 2
B3: 3
B4: 4
Input cell: C1: "I like pears"
https://docs.google.com/spreadsheet/ccc?key=0Aj54xKrDDiaFdGt5bHBQTDhXU1prSktuQ0FvelJpTWc
How can I create a formula that will return a value of "1" because cell C1 contains the word "pears"?
I know how to use VLOOKUP to match an exact value (eg =VLOOKUP("pears" ; A1:B4 ; 2 ; FALSE() ) but I'm not sure how to match it if the input cell contains a text string that matches one of the reference cells.
The application is for a home finance spreadsheet that automatically assigns a category if the transaction name (long string) contains a shorter string that is in a separate list.
----------------------------------

following is one solution to the problem ...

yogi_Fetch A Value Based On A Date Range

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user thirdbridge said:
Fetch a value based on a date range
On Sheet1, I have a column of bagels. For each type of bagel, there is a series of date / baker's dozen price / single price groups of data - where the date in each triple column group represents the date the new price for the baker's dozen went into effect. The single column is a baker's dozen calculation simply dividing by 12 to get the single price. See below:
A (Bagel) B (Date) C (12) D (1) E (Date) F (12) G (1) H (Date) I (12) J (1)
1 Plain 6/1/11 $15 $1.25 8/19/11 $17 $1.42 11/1/11 $22 $1.83
2 Blueberry 4/15/11 $15 $1.25
3 Everything 6/1/11 $15 $1.25 10/12/11 $17 $1.42
4 Whole Grain 5/25/11 $15 $1.25 7/19/11 $19 $1.53 11/1/11 $22 $1.83
On Sheet2, I have a report with a date at the top. The date falls between the dates listed above.
How can I reference the dates on Sheet1 and return the corresponding single bagel price that fits the date listed on Sheet2?
For example: if the date on Sheet2 is 9/1/11, how do I show that the price of the Plain bagel on that day was $1.42?
Thanks!
---------------------------------

follwing is one solution to the problem ...

yogi_Pull Data Into SummarySheet From Client Sheets In Different Spreadsheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

user Jojoba123 said:
I am currently using Google Docs to manage my clients. Every client gets their own sheet in my spreadsheet. I created a summary sheet that uses the INDIRECT function to dynamically refer to my client sheets in the spreadsheet. I am quickly approaching my sheet limit in the spreadsheet. What I want to do is to have a separate summary sheet spreadsheet and use INDIRECT to refer to  a different spreadsheet that contains my client sheets.

I have looked extensively through these forums to solve my issue, it seems like the INDIRECTfunction does not allow referring to another spreadsheet. The closes thing I found was theIMPORTRANGE function, that used part of the spreadsheet URL to link between spreadsheets. If this was Excel the indirect formula would look something like this:indirect("book1.xls!"&PitcherNameCell),30,1)The "book1.xls!" being the most important bit to refer to the workbook (in Google Docs terms: spreadsheet) that I want to get information out of.I appreciate any help, even if it is to just explain to me that Google Docs cannot do this, so that I can begin looking for another way to manage my clients.

Here is an example to see what I currently have set up, so you can get an idea of what I want to accomplish.https://docs.google.com/spreadsheet/ccc?key=0AvuVMsSOGJSwdFUxQnFHSEZlVHRmcWptcml0dlBTT0E

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

I now realize that despite the example spreadsheet provided by Jojoba123 where he showed the SummarySheet and the CLIENT sheet(s) within the same spreadsheet he wants to use these in different spreadsheets ... so with this as the background I have posted a solution for pulling in the CLIENT data into SummarySheet ... and with the assumptions that I made and the formulation I used I did not even have to use the INDIRECT function as shown in my blog post:


Earlier, assuming that Jojoba123 had SummaySheet and ClIENT sheets within the same spreadsheet I had posted a solution in yogi_Pull Data Into SummarySheet From Client Sheets Within The Same Spreadsheet

yogi_Pull Data Into SummarySheet From Client Sheets In Different Spreadsheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

yogi_Pull Data Into SummarySheet From Client Sheets Within The Same Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Jojoba123 said:
I am currently using Google Docs to manage my clients. Every client gets their own sheet in my spreadsheet. I created a summary sheet that uses the INDIRECT function to dynamically refer to my client sheets in the spreadsheet. I am quickly approaching my sheet limit in the spreadsheet. What I want to do is to have a separate summary sheet spreadsheet and use INDIRECT to refer to  a different spreadsheet that contains my client sheets.

I have looked extensively through these forums to solve my issue, it seems like the INDIRECTfunction does not allow referring to another spreadsheet. The closes thing I found was theIMPORTRANGE function, that used part of the spreadsheet URL to link between spreadsheets. If this was Excel the indirect formula would look something like this:indirect("book1.xls!"&PitcherNameCell),30,1)The "book1.xls!" being the most important bit to refer to the workbook (in Google Docs terms: spreadsheet) that I want to get information out of.I appreciate any help, even if it is to just explain to me that Google Docs cannot do this, so that I can begin looking for another way to manage my clients.

Here is an example to see what I currently have set up, so you can get an idea of what I want to accomplish.https://docs.google.com/spreadsheet/ccc?key=0AvuVMsSOGJSwdFUxQnFHSEZlVHRmcWptcml0dlBTT0E

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

I am not quite clear on user Jojoba123's requirements ... the user talks about different spreadsheets but in the example the user has provided the SummarySheet and the Client sheets are within the same spreadsheet. In the following solution to the problem I have assumed that the SummarySheet and the Client sheets are within the same spreadsheet

yogi_Sum Up Attribute In Column C For Names In Column B For Specified Dates

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user robjwill said:
Google equivalent to Sumifs too big for application - Need a smaller solution!
I have 60 People that I need to keep track of credits they purchase.  I need to enter the date they they purchase the credits, their initials and the number of credits purchased.  The inputted data is as follows:
    A           B     C
1  11/15/2011  LL    10
2  11/17/2011  RW    20
3  11/15/2011  SH    20
4  11/16/2011  VF    10
5  10/1/2011   AB    20
6  10/1/2011   RW    10
....
90 8/17/2011   SH    20
I need to track the total credits purchased by all 60 people daily, counting down from the current date (using the TODAY() function), keeping a history of 3 months of credit.  The resulting summary report should look as follows:
              RW   SH   LL   VF   AB   JB   IJ .................  person60
11/17/2011    20
11/16/2011                   10
11/15/2011         20   10
...
10/1/2011     10                       20
...
8/17/2011              20
---------------------------------
following is one solution to the problem ... I have generalized in terms of number of names and number of days preceding today's date

yogi_Compute Sum By WeekNumber For A Given Range Of Dates and Amounts

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user gassiep said:

get a weeknumber of a date for grouping with query I want to use a spreadsheet as a source for the query function. =query(sheet!A:B ; "Select ???? , sum(B) group by ????")  I have this data: A            B  1-1-2011    12 1-30-2011   34 2-12-2011   23 With the query function I want to sum coulm B by weeknumbers of the date in column A.  I also tried creating an extra column C which uses the text function for the weeknumber, but then the query gives a error saying column C doesn't exist in the source :(How can I do this? -----------------------------------------------------------------

following is one solution to the problem wherein I create a virtual column of weeknumbers and then use the QUERY function to group by weeknumber and sum up the corresponding amounts:

yogi_Query A Range And Suppress Display Of Specified Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Artiom Neganov said:
How to remove column from array?
I have a query returning 2-dimensional array like:

=query('Sheet1'!A1:J;"select * where Col3='value'"))

How can I remove a 3rd column from the output?

How can I remove a 3rd an 5th column from the output?

Sorry for asking probably stupid questions here - I just can't find good function reference on Google Spreadsheets.

----------------------------------------------------------------------------
As my esteemed colleague and fellow Top Contributor A.P.L. (aka Adam) at Google Docs Help Forum said
Not stupid questions at all. :-)
and I am sure Andre (aka ahab) my esteemed colleague and Google Docs Guru at Google Docs Help Forum, where he is mentor to most Google spreadsheet enthusiasts, and the soul of the Google spreadsheet Help Forum (about whom we are really concerned because we have not heard from him now for about a couple of months) would agree that there is no such thing as a stupid question!


Following is one generalized solution to the problem ...
Sheet1 shows data to be QUERYed , Sheet2 shows the result after executing the QUERY function

yogi_Sort A Column Of Dates By WeekDay

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user accquestion said:
How do I sort columns to read Monday-Saturday instead of only in ascending & descending alpha order?
------------------------------

solution to the problem:
the user has not provided specifics as to what the user is working with ...
I am going to consider a case where we have a set of dates in column A and we are going to sort the dates in column A by weekday

yogi_Sort Columns By Day Of Week

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user accquestion said:

How do I sort columns by days of the week Monday-Friday rather than by ascending & descending alpha order?

When downloading to Google Docs, how can you list the days in order Monday-Saturday in columns, now you have to choose alpha order only.  This is what it looks like now:

Friday Monday Saturday Thursday Tuesday Wednesday Grand Total
  0      0       0        0        0        0
  1      5       3        4        1       14
  5      9      13        5       32
-----------------------------

following is one way for solution to the problem

yogi_Create A Table Of Filtered Entries Based On Criteria From Two Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user joshnekrep said:
I would like to create a worksheet that counts the number of entries in another sheet that match any of the entries in a third sheet.
For example:
Sheet 1:
USERNAME, DATA, DATA
Jim, whateve, whatever
Frank, whatever, whatever
Sally, whatever, whatever
Bill, whatever, whatever
Jim, whatever, whatever
Sheet 2:
USERNAME, TEAM NAME
Jim, Team 1
Frank, Team 2
Sally, Team 1
I would like to create a sheet that considers Sheet 1, and looks for all the instances of any username IF it is listed on Sheet 2 (ignoring those not listed) and then counts the number of occurrences for each Team Name (not each user).
The spreadsheet I'm working with polls Twitter for a specific hashtag, but I'd like to consider only those usernames that have been registered, and apply points to the team that the user is on.
Here is the spreadsheet I'm working on: https://docs.google.com/spreadsheet/ccc?key=0Ak_gj2-lxusydEI5Q012SXp6c055REVuTVlYc2NsNmc
I'd like the results to show up on the Team Counts sheet.
Hope this all makes sense and someone can help.
Thanks!
Update:
For clarification, in my above example the results I'm looking for would be as follows:
Team Counts:
Team 1, 3
Team 2, 1
Team 3, 0
etc...
------------------------------
following is one solution to the problem

yogi_Highlight Rows That Have Duplicate Content In A Specified Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Ryan-JaguarComm said:
Now I've got a new mission.
Make the sheet tell me if Duplicate data exists and if it does, highlight both entries in some way so it stands out.
That way we know if duplicate entries on devices are being made. 
Till building the structure of these Functions click, I'm going to have a heck of a time. 
Unfortunately I can't share the data. I can however try to better explain.
There is 8 columns of data. A-H
Row Data is to be kept together, each row is a form submitted entry.
The data is being collected from 4 different towns. Which leaves me currently with 5 sheets.
1 Master (Form Entry)
4 Cities sheets, simply pulling  filtered data from the master (as described above)
To take the Cities sheets a step further, I want to look at all the Filtered data, and have it highlight the cell, or the row of data based on data in one column that matches.  
ex: If Row 5 and row 7 have the same number in column D, show them both in red. Don't matter if its Colored Text or background as long as it stands out.
----------------------------------------------
In the following proposed solution to the problem, I highlight the duplicates by coloring the background of duplicated rows in an adjacent column

yogi_Assign 1 If A Person Submits Entry By DeadLine Else Assign 0

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Estesark said:
How do I check for the last entry before a deadline?
I have a form, which people on a forum I visit use to submit answers. I have multiple sheets on the form spreadsheet to manipulate the data, but there is one thing I can't quite work out how to do: find the last entry before a deadline for each person. I need to be able to do this because if a person submits a new set of answers, the old ones are irrelevant. Because all the entries are timestamped, I can check whether they were submitted before a deadline, which I enter manually into a cell, using a simple formula such as IF(C3<=$C$2,1,0), where column C contains the dates and cell C2 contains the deadline.I can also check whether they submitted any answers afterwards with formulas likeIF(COUNTIF(E5:$E$20,E4)=0,1,0), where column E contains their names.What I'm not sure about is how to combine the two, to give me a "1" value for the last entry submitted by each person before the deadline, and "0" values for earlier entries and entries after the deadline. I can't just multiply the two IFs together; I feel like there has to be some sort of lookup involved, but I'm not sure what it is.Please let me know if you need me to provide more detail. Thank you!

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

following is one solution to the problem

yogi_Assign 1 If A Person Submits Entry By DeadLine Else Assign 0

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user Estesark said:
How do I check for the last entry before a deadline?

I have a form, which people on a forum I visit use to submit answers. I have multiple sheets on the form spreadsheet to manipulate the data, but there is one thing I can't quite work out how to do: find the last entry before a deadline for each person. I need to be able to do this because if a person submits a new set of answers, the old ones are irrelevant. Because all the entries are timestamped, I can check whether they were submitted before a deadline, which I enter manually into a cell, using a simple formula such as IF(C3<=$C$2,1,0), where column C contains the dates and cell C2 contains the deadline.I can also check whether they submitted any answers afterwards with formulas likeIF(COUNTIF(E5:$E$20,E4)=0,1,0), where column E contains their names.What I'm not sure about is how to combine the two, to give me a "1" value for the last entry submitted by each person before the deadline, and "0" values for earlier entries and entries after the deadline. I can't just multiply the two IFs together; I feel like there has to be some sort of lookup involved, but I'm not sure what it is.Please let me know if you need me to provide more detail. Thank you!

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

following is one solution to the problem

yogi_Create A Dependent DropDown List Of Items Based On A List Of Items In A DropDown List

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
Earlier I had created a post yogi_Create A Dependent List Of Items Based On A List Of Items In A DropDown List
in this post I am going to Create A Dependent DropDown List Of Items Based On A List Of Items In A DropDown List (WOW ... a mouthful that sounds confusing to me!)

Well following is one solution to the problem