With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.

Wednesday, November 30, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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 = 118a2 = 123a3 = 129a4 = 132a5 = 135a6 = 140a7 = 146Between 118 and 123 is range1Between 123 and 129 is range2Between 129 and 132 is range3Between 132 and 135 is range4Between 135 and 140 is range5and 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.---------------------------------------------------

Tuesday, November 29, 2011

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 Steps1 92 223 18....10 4Just want to do some basic matrices showing how many records are in a particular step And help would be greatly appreciated!--------------------------------------------------

Sunday, November 27, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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 Visit2/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.
--------------------------------------------------------------

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuse 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

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser BrighterLater said:Convert column of email addresses to hyperlinksIs 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 Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comThis 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 spreadsheetAs 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 Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comThis 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 spreadsheetAs 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.

Saturday, November 26, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser jamiewig said:Query on date cleanedI 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-----------------------------------------------------

Saturday, November 19, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser DoctorBean said:Use VLOOKUP if the reference matches only a part of the search criterionI'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: pearsA2: applesA3: orangesA4: grapesB1: 1B2: 2B3: 3B4: 4Input cell: C1: "I like pears"https://docs.google.com/spreadsheet/ccc?key=0Aj54xKrDDiaFdGt5bHBQTDhXU1prSktuQ0FvelJpTWcHow 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.
----------------------------------

Friday, November 18, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser thirdbridge said:Fetch a value based on a date rangeOn 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.832 Blueberry 4/15/11 $15 $1.253 Everything 6/1/11 $15 $1.25 10/12/11 $17 $1.424 Whole Grain 5/25/11 $15 $1.25 7/19/11 $19 $1.53 11/1/11 $22 $1.83On 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!
---------------------------------

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:

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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

Thursday, November 17, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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 C1 11/15/2011 LL 102 11/17/2011 RW 203 11/15/2011 SH 204 11/16/2011 VF 105 10/1/2011 AB 206 10/1/2011 RW 10....90 8/17/2011 SH 20I 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 ................. person6011/17/2011 2011/16/2011 1011/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

get a weeknumber of a date for grouping with queryI 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 121-30-2011 342-12-2011 23With 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:

----------------------------------------------------------------------------As my esteemed colleague and fellow Top Contributor A.P.L. (aka Adam) at Google Docs Help Forum saidNot 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

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

Friday, November 11, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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, DATAJim, whateve, whateverFrank, whatever, whateverSally, whatever, whateverBill, whatever, whateverJim, whatever, whateverSheet 2:USERNAME, TEAM NAMEJim, Team 1Frank, Team 2Sally, Team 1I 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-lxusydEI5Q012SXp6c055REVuTVlYc2NsNmcI'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, 3Team 2, 1Team 3, 0etc...
------------------------------
following is one solution to the problem

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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-HRow 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

Thursday, November 10, 2011

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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!

Yogi Anand, D.Eng, P.E.Google Spreadsheet www.energyefficientbuild.comuser 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!