## Thursday, January 31, 2013

### yogi_Make Computations By Inserting Computed Columns Into A Form Responses Sheet

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 31, 2013
How to submit form, and keep formulas?
Hi I am trying to setup a form, that when it goes into my google spreadsheet formulas are applied to the data entered.

If you look at the Spreadsheet Columns A-D are populated when the form is submitted, then I want E to populate with NOW() to show the current time.
In Column F I want a formula to subtract A and E to display How much time has lapsed since the form was submitted and the current time.
In Column I I am using the formula =IF(AND(F2>2,H2="Open"),"Working",)
This is working fine if the data is entered into the spreadsheet, but when someone submits the form the fomula's are not applied to the new lines.  Any suggestions on how to get the formula's to apply to all form submissions?

Thanks,
Brandon

-----------------------------------------------------------------------------------------------
following is my proposed solution to the problem

### yogi_Do MultiConditional Count Using Alernate Formulations Using Functions Such As QUERY FILTER SUM COUNTA

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 31, 2013
How to change this select query into a count query?
Hi all,

I've just wrote a query like this:
=QUERY(Guests!\$A\$1:\$D\$300, "SELECT C WHERE A='"&A2&"' AND B='"&B2&"' AND D!='No'")

It returns 3 rows of results that fits the requirement.
Is there anyway I can change this query so it can give me the count number (3) instead of the 3 rows?

Thanks.

---
I tried this one
=QUERY(Guests!\$A\$1:\$D\$300, "SELECT COUNT(C) WHERE A='"&A2&"' AND B='"&B2&"' AND D!='No'")

It doesn't work the way I want.
Instead of a 1 cell result, it gives me 2. One of them says "count" and the other one says "3".

So is there anyway to achieve the result I need?

------------------------------------------------------------------------------------------
following is a solution to the problem

### yogi_Compute The Facility Name From Data Not Laid Out As A List (Database)

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 31, 2013
Help - how to search up the column
Hi,

I'm trying to display all the tasks that match up the specific day (F2) on the columns G to F. The problem I have is that I don't know how to pull in the house # information along with the task (in green).

The amount of tasks for each house can vary, and it isn't always called a house or mansion.

I was thinking that I need to search up the column until I find the next row where start date is blank, but I don't know how to do that. I'm also open to any possible suggestions.

Thanks!

---------------------------------------------------------------------------------------------------
following is my convoluted solution to the problem using a helper column (that can be hidden)

### yogi_Set Up Computed Column K For Row By Row Average Of Values In Columns H I And J

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

I have made a form and I want to be able to see the average of 3 answers automatically in the row next to it.
I want to be able to but the answers in a chart later but I’ll be able to do that myself.
-------------------------------------------------------------------------------------------------
following is a solution using several alternate formulations

## Wednesday, January 30, 2013

### yogi_Compute MultiConditional Sum Using Various Functions Such As SUMPRODUCT SUM FILTER And QUERY

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 30, 2013
post by user: Smartgeek and question by Abdelkrim METRAB said:(http://productforums.google.com/forum/?zx=7oinkl81cee#!mydiscussions/docs/UA5o5zfXBdY)
sumproduct just doesn't work at all !!!
Hi guys,

I just can't get away from this endless error in using SUMPRODUCT formula.
As you see I'm clear in putting the formula into the sheet, however it doesn't work.
I want to get the score of steve in July. FYI it works well in excel but google sheet.
=sumproduct((month(F23:F26)=7)*(E23:E26=E23)*(G23:G26))
Anyone can help me figure out this please?

------------------------------------------------------------------------------------------
following is a solution using various alternate functions such as SUM SUMPRODUCT FILTER and QUERY

### yogi_Compute Grades For Quizes With Open Ended Number Of Questions And Specified Points For Different Quizes

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 06, 2013

this is related to the solution I provided to Neal Boocock's question in my following blog posts:

yogi_Compute Student Score For Each Quiz And Then SumUp The Scores For All The Quizes For Each Student
http://yogi--anand-consulting.blogspot.com/2013/01/yogicompute-student-score-for-each-quiz.html

and

yogi_Compute Student Scores For Quiz 1 And Quiz 2 And Present Results In Sheet yogi_GradeSheet2

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

here I provide a solution -- the number of questions in each Quiz is open-ended, and points for correct answer for each Quiz is as specified in sheet named points

## Tuesday, January 29, 2013

### yogi_A Single Formula WorkAround For Using NETWORKDAY Function For An Array Of Values

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 29, 2013
workdays with 2 columns
Hi, I just wanted to know if there is a way to see the workdays of 2 diff columns and the answer in a third column, like workdays startdates would be in comlumn A and finish date would be in column B and column C would have the answer of each pair.
---
yes, I mean those days, I know the networkdays function can give me that, but I cant grab 2 columns, I have to do it one pair of date at a time, like NETWORKDAYS(A1,B1)  NETWORKDAY(A2, B2), I wanted to do something like NETWORKDAYS (A, B) refering the dates from A1 to AX as startdate and from B1 to BX as finish date, and the column C would have the result of each of the NETWORKDAYS(Ai, Bi)  like Ci = NETWORKDAYS (Ai, Bi) for i = 1 to i=n.
--------------------------------------------------------------------------------------------
NETWORDAY function can not be used with an array of values ... so in the following I have presented a single formula  WorkAround for computing Net Working Days for an array of Start and End dates

### yogi_Show Numeric Entries In Column A Into Column B With + - Sign And Leave Text Entries As Is

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 29, 2013
Positive numbers + sign
Is there a way in Google Spreadsheets to format a positive number e.g. +4 instead of 4
----------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem where I have also considered negative numbers as well as TEXT entries

## Monday, January 28, 2013

### yogi_Compute Row By Row Running Totals Of Medical Reimbursements Requests And Remaining Benefits

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 28, 2013
array filter a sum
hello

shared document

i want to mail merge...i can get the correct values if i pull it down for the column I... but if i add to the array with a form it wont update that value....It is left blank....
Help me please understand the function of the array and the fliter and sum fucntions
Thanks
-----------------------------------------------------------------------------------------------------

matrixband: I don't know what you mean by mail merge here ... in the following I have provided a solution based on my understanding of what you are looking for

## Sunday, January 27, 2013

### yogi_Compute The Date For American And Canadian ThanksGiving For A Specified Year

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 28, 2013
calculating thanksgiving for a given year
I'm having a difficult time coming with a formula to calculate the thanksgiving date (display as 28-Nov for example) for a given year.  Anyone know how to do this?  Thanks!
-----------------------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem

### yogi_Extract Unique Values From Column A Of Sheet1 Sheet2 Sheet3 Filtering Out Entries Greater Than A Specified Number And Sort In Ascending Order

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

I ran into an additional problem.

I need to filter out any number above 3999 that gets retrieved from the other sheets. I can't wrap my head around how do it with such a complex formula.

=ArrayFormula(sort(unique(TRANSPOSE(SPLIT(CONCATENATE(Jan!A2:A&CHAR(9);Feb!A2:A&CHAR(9);Mar!A2:A&CHAR(9);Apr!A2:A&CHAR(9);Mai!A2:A&CHAR(9);Jun!A2:A&CHAR(9);Jul!A2:A&CHAR(9);Aug!A2:A&CHAR(9);Sep!A2:A&CHAR(9);Okt!A2:A&CHAR(9);Nov!A2:A&CHAR(9);Des!A2:A&CHAR(9));CHAR(9))))))
---------------------------------------------------------------------------------
assuming entries in column A of Sheet1 Sheet2 Sheet3 are all numeric, following is a solution to the problem

### yogi_Sort A List Using Sort Function And Alternately Using Query Function

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 27, 2013
I have a spreadsheet and I would like to sort each row alphabetically according to the information in column A. The information in each row of column A is a country name. The information in each row of column B is a positive rational number.

First, I clicked the top left corner of the sheet to select all the cells. Then, from the data menu I select "Sort sheet by Column A, A to Z" Then nothing happens; my spreadsheet is unchanged.
I have also tried selecting only column A, only column A and B, right clicking and selecting "sort range", and a variety of other methods. Nothing seems to effect the order of information on the table.

If someone could explain how to do this properly I would appreciate it. Thank you.

---

 William Rafferty Post reply 9:05 PM (44 minutes ago)

I have made the document accessible to anyone with a link to the document. The link above is a link to a copy of the document. The information is contained in the first 204 rows and 2 columns.

I would like to sort the list in Column A alphabetically, but I would like the information in each row to stay with the information immediately adjacent to it.

The information is initially similar to the following:
Column A  Column B
Monaco                 0
Japan                   .4
Belize               41.4
Honduras          91.6

After the process I described earlier where I try to alphabetize the information, the information is identical.

My expected result is that the information would look similar to the following:
Column A  Column B
Belize               41.4
Honduras          91.6
Japan                   .4
Monaco                 0

My logic is that using the function "Sort sheet by column A, A to Z" or a similar function would result in the rows of the table being sorted according to their alphabetical order within column A because the name of the function seems to me that it would sort the rows of the table according to their alphabetical order within column A.

I appreciate your reply. Thank you for taking the time to read this and provide advice. I suspect it may either be a problem with my computer or I could be overlooking an obvious detail. Thank you for your help. Hopefully you will be able to do what I have not been able to do
-------------------------------------------------------------------------------------------------------------

This is a simple problem requiring the use of SORT function or the QUERY function ...
since the user is having a lot of trouble with it, I present an illustration as follows

### yogi_Extract Unique Values From Column A Of Sheet1 Sheet2 Sheet3 And Sort In Ascending Order

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 27, 2013
Sort unique from multiple sheets in one formula

This works for one sheet:

=sort(unique(Sheet1!A2:A))

I need to combine data from (Sheet1!A1:A),  (Sheet2!A1:A) and (Sheet3!A1:A)  etc.. and make ONE list that sorts and removes duplicates.

Anyone know how to do this?
--------------------------------------------------------------------------------------------------------
following is a solution to the problem

## Thursday, January 24, 2013

### yogi_Search Column A For Specified String And In Case of Match Return Entries In Column B

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 24, 2013
How do I recognise a pattern in spreadsheet?

Hi
I am working in a spreadsheet and I need to lookup a value e.g. 200 A U406 ICF-CHK 31AUG10_G held in cell A1
 and return the full title referenced in another sheet and return the full title. Full title generally looks something like this 200 A U406 ICF-CHK 31AUG10_G Informed Consent Form Checklist for Main Informed Consent Form as you can see only the first part is the same and sometimes the length differs.  I have tried using FIND, MATCH etc but am struggling!  Any help very much appreciated :) ----------------------------------------------------------------------------------------------

following is a solution to the problem

## Tuesday, January 22, 2013

### yogi_From A Table Of Name And Attributes Concatenate Attributes By Name Row By Row

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 23, 2013

Can this be done in Google Spreadsheet? Highlight due dates in Excel – Show items due, overdue and completed in different colors
-----------------------------------------------------------------------------------
following is a solution to the problem

### yogi_WorkAround For Appplying Conditional Formatting In Google Spreadshet Based On Multiple Formula Based Condtions

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 22, 2013
Can this be done in Google Spreadsheet? Highlight due dates in Excel – Show items due, overdue and completed in different colors http://chandoo.org/wp/2012/05/22/highlight-due-dates-excel/
---------------------------------------------------------------------------------------

### yogi_Pull In Records From Log-File That Are No Older Than Specified Number Of Days

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Jan 22, 2013
Solution to auto-hiding rows
_________________
Hey guys

Below is a short introduction, if you wanna skip that and go straight to the answer, you can look after the headline "QUESTION"

Introduction
I need some help(surprise!).
I have a sheet like this(this is a public copy);

It has a couple of tabs with different sheets that serves different purposes.
In general, it is a sheet to keep track of donations/withdrawals into a shared storage in a game.

However, after a a couple of months time, this sheet is populated by more than 1000 rows of inputs, which is a bit much to scroll through.
Which is why I have started using the "Hide rows"-function in the sheet - now we're getting closer to my question!

I would like this sheet to require minimal service/maintenance, so what I'm wondering is:

Question
Is there a way (scripting, built-in function etc.) to make the sheet automatically hide entries(based on the timestamp) older than fx. 7 days?

I currently have a script in the sheet, which I have constructed from bits and pieces of other scripts, since I'm no pro with scripting. This script is triggered by "onFormSubmit", so I was wondering if it was possible to do something like this for the sheet:

For all rows
OnFormSubmit
IF timestamp IS OLDER THAN 7days
THEN HideRow

I know the above is not written in correct syntax and/or terms, but it's just go give you an idea of the desired functionality. And yes, I know it's not much of a job to go in and manually hide the rows each day/once a week, but it's just as much to learn some scripting as it is to ease my everyday :)

--------------------------------------------------------------------------------------
following is a formula based solution to a bit more generalized problem