yogi_Make Computations By Inserting Computed Columns Into A Form Responses Sheet

                                          Google Spreadsheet   Post  #1002

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

post by user: Brandon Fishman said:(http://productforums.google.com/forum/?zx=7oinkl81cee#!category-topic/docs/spreadsheets/UEBn0I5w5pM)
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.  

You can see my spreadsheet at: https://docs.google.com/spreadsheet/ccc?key=0AnkVRC1UPDUGdFV4bU9fWnhaX0x3bmFuakZGMzlvN1E&usp=sharing

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

                                          Google Spreadsheet   Post  #1001

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

post by user: Koala Yeung said:(http://productforums.google.com/forum/?zx=7oinkl81cee#!category-topic/docs/spreadsheets/qzMH8c6JTvU)
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)

                                          Google Spreadsheet   Post  #1000

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

post by user: SimonNY said:(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/AB_eo_7iCnA)
Help - how to search up the column
Hi,

I have a Test spreadsheet here: https://docs.google.com/spreadsheet/ccc?key=0AmrjXVhLBq5NdGlVSzJPbURjV0prdlQtT3JNUHEyWkE#gid=0

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

                                          Google Spreadsheet   Post  #999

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

post by user: Wouter Schol said:(http://productforums.google.com/forum/?zx=7oinkl81cee#!category-topic/docs/spreadsheets/-IjvF5A1ci8)

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.

Please help me

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

following is a solution using several alternate formulations

yogi_Reference A Cell Value In Where Clause Using QUERY Function

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

                                          Google Spreadsheet   Post  #997

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

                                          Google Spreadsheet   Post  #996

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

user Neal Boocock said:(http://productforums.google.com/forum/?zx=6xwd73m1l93e#!category-topic/docs/spreadsheets/xAxD8urYDqo)

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

http://yogi--anand-consulting.blogspot.com/2013/01/yogicompute-student-scores-for-quiz-1.html

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

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

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

                                          Google Spreadsheet  Post  #995

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

user luis seyler said:(http://productforums.google.com/forum/?zx=4yxtidi1aog8#!category-topic/docs/spreadsheets/eMybzyMnSTs)
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

                                          Google Spreadsheet  Post  #994

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

user Paul Curtis said:(http://productforums.google.com/forum/?zx=605ui8ts3fnb#!category-topic/docs/spreadsheets/wT8HWdxuN1o)
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

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

                                          Google Spreadsheet  Post  #993

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

user matrxband said:(http://productforums.google.com/forum/?zx=s55ci3m9dnfd#!mydiscussions/docs/bvascQJIoM0)
array filter a sum
hello

https://docs.google.com/spreadsheet/ccc?key=0AlcUYG1a-pUodG9KdDVIRXY1Z0MyTWlUb2VqZjdCYXc

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

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

                                          Google Spreadsheet  Post  #992

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

user Marcos Pinto said:(http://productforums.google.com/forum/?zx=rq2ugebf7h4a#!category-topic/docs/spreadsheets/zCmH8rQMVPQ)
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

                                          Google Spreadsheet  Post  #991

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

user Testus said:(http://productforums.google.com/forum/?zx=rq2ugebf7h4a#!category-topic/docs/spreadsheets/BgPI0d9qyQ8)

this post addresses another question in the preceding thread ...

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

                                          Google Spreadsheet  Post  #990

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

user William Rafferty said:(http://productforums.google.com/forum/?zx=rq2ugebf7h4a#!category-topic/docs/spreadsheets/3nDPfxp4UyM)
Google Spreadsheet Alphabetical Sorting Trouble 
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)

https://docs.google.com/spreadsheet/ccc?key=0AtOnpEHYx0hhdEVGUl8xQjY5bk4xN0k0NVpHYWJYbXc

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

                                          Google Spreadsheet  Post  #989

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

user Testus said:(http://productforums.google.com/forum/?zx=rq2ugebf7h4a#!category-topic/docs/spreadsheets/BgPI0d9qyQ8)
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

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

                                          Google Spreadsheet  Post  #988

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

user nelipot said:(http://productforums.google.com/forum/?zx=1bjq25opi7sr#!category-topic/docs/spreadsheets/mql5YU64wYU)
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

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

                                          Google Spreadsheet  Post  #987

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

user djograd dldealday said:(http://www.vcita.com/engagements/676879?email_token=39ed60aee265bbbe7d13)

Can this be done in Google Spreadsheet?

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/

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

following is a solution to the problem

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

                                          Google Spreadsheet  Post  #986

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

user djograd dldealday said:(http://www.vcita.com/engagements/676879?email_token=39ed60aee265bbbe7d13)
Can this be done in Google Spreadsheet?
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

                                          Google Spreadsheet  Post  #985

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

user Stente said:(http://productforums.google.com/forum/?zx=498o8ekdutdo#!category-topic/docs/spreadsheets/orZbnTKPUWI)
Solution to auto-hiding rows

Win7 & Google Chrome 24

_________________

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

https://docs.google.com/spreadsheet/ccc?key=0Al1R2aPikIR0dGpYLU8zZ2M2aEZkV3dObG9EdS1QbEE#gid=0

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.

Now, this sheet gets inputs from a form (https://docs.google.com/spreadsheet/viewform?formkey=dGpYLU8zZ2M2aEZkV3dObG9EdS1QbEE6MA).

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