yogi_Pull Date From Timestamp Column If Rows In A Specified Column Do Not Have A Date Entry

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

user Bill_ said:
Form Data Auto Date Extraction
I set up an open spreadsheet with a live form to aid in getting my problem worked out. Free free to play with it, etc. It's open and buried in a collection specifically for testing and finding a solution for me and any future searchers.
Here is the live form:
https://docs.google.com/spreadsheet/viewform?formkey=dHhRSzlkZkpSNTctdlBrZXctTTJFR1E6MQ#gid=0
Here is the spreadsheet:
https://docs.google.com/spreadsheet/ccc?key=0AhTPJ4zL-mX0dHhRSzlkZkpSNTctdlBrZXctTTJFR1E
I’m trying to transfer the forms data collection to another page. In doing so, I’m trying to have the query or arrray automatically fill in the Date with the timestamps date if the user didn’t fill in the date.
I realize I can require this question, but that is not my goal with this.
Is it possible to do something like this?
------------------------------------------
following is a solution to the problem

yogi_Publish A File Without Any Title Or Standard Statements At The Bottom

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

I thought the best way is to illustrate this by taking the actual file and publish it to see that it does meet the user specified publishing specification ... so here we go -- in the following solution, I used a Google Docs spreadsheet, then I published a single sheet with a specified range with widget=false in the resulting iframe code.
--------------------------------------------------

yogi_Create A Workaround To Count The Number Of Cells Where Hyperlink Function Has Been Used

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

user DSMader said:
Using Countif to count instances of a formula
Is it possible to use COUNTIF to count the number of instances of a formula, rather than the output of that formula, within a cell range?
Here's my situation: Column A of my spreadsheet lists 308 items. Subsequent columns are used as "check-boxes" to indicate whether a given item meets a certain criterion. If an item meets a given criterion, I add a hyperlink to the item cell in column a using HYPERLINK, so that readers can click on the item to read the source supporting the proposition that the item meets the relevant criterion.
Only a subset of the 308 items meet the relevant criteria and therefore have hyperlinks. I'd like to use COUNTIF to automatically count the number of items that meet the relevant criteria, and automatically add additional items.
I've tried the formula =COUNTIF(A2:A309, HYPERLINK) but get a range name error on 'hyperlink'; I've also tried =COUNTIF(A2:A309, "hyperlink") and received a 0 value - presumably because the word "hyperlink" does not appear in the output text in any of the cells in the range.
So: is there a way to have a formula be the criterion in the COUNTIF function?
Thanks!
---------------------------------------------
following is a proposed workaround solution to the problem

yogi_Determine Unique Count Of Entities Based On Characters A to Z Only Regardless Of Case

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

user timerecords said:
Help writing a function
So I've written a doc that has all of the top movie lists as different sheets(1001 movies to watch before you die, afi top 100, imdb top 250, etc). It is set to where a person can go in and click on each sheet and put a 1 in a cell next to the movie if you've seen it and I have data accrued from there(amount seen, amount unique seen for all users, etc).
Currently I've got each list in a different sheet and the user goes through and clicks if they've seen it. I want to have one central list with all the movie titles and all people have to do is look through it once, and click if they've seen it and it will fill out the rest of these lists.
My issue is that when i copied the lists onto the sheet the formats weren't all the same, i.e. one would say Godfather, one would day GODFATHER, one would say Godfather(1972).
My question, how do I pull out all of the unique films(each film only listed once) from all these lists and not pull out three different titles for the same film? The rest I can do.
Please help,
Tim
------------------------------------------------------
following is a solution to the problem

yogi_Pick A Set Of Random IDs As Percentage Of IDs Assigned To A Set Of Names

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

user statq said:
I want a set of random ids picked against a subset of names, and this random sample should be percentage of the subset.
I want to get a list of random IDs corresponding to Alex, Adam, Steve. This list of random Ids should be 20% of the total number of Id under Alex.
Example : Alex has a total of 6 IDs that he has worked on, now I want a random sample of 20% of that 6 which is around 1, if he is worked on 100 IDs then I want the query to pick up a random sample of 20(20% of 100) IDs that Alex has worked on and 20% of IDs that Adam has worked on and so on.
Right now the following query works to get a list of ransom samples under say Adam alone.
=ARRAYFORMULA(SORT( A2:B7 ; RANDBETWEEN( SIGN(ROW(B2:B7)) ; 1E293 ) ; 1 ))
https://docs.google.com/spreadsheet/ccc?pli=1&key=0AlqKm4fWq06OdGpPVnVmNk1iNEpmRktaMHdCcGtlTGc#gid=0
Wanted some suggestions and help. Thanks
-------------------------------------------------------------
following is a solution to the problem

yogi_Extract Name And Address Rows With Unique Addresses Only

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


user Cullen MI said:
Filter unique rows, but include a row that may not be unique
My main sheet is simply a list of names and addresses. I want to end up with a sheet of unique addresses to use as mailing labels.
However, a lot of the entries are members of the same family at the same address. I might have:
  A     B           C             D      E    F
Joe   Smith  123 Main Street  New York  NY  10010
Jane  Smith  123 Main Street  New York  NY 10010
I would like to keep one of those addresses, and just select one of the names to use.
In my second sheet I can do Unique(A:F). But this will return both of those lines, since they are not entirely duplicates.
I could also do Unique:(C:F), which will return just one copy of the address, but then I lose the name.
How can I get one address, with one name?
---------------------------------------------------------
following is a solution to the problem 

yogi_Develop Formulas For Fraction Rounding

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

user theDante said:
formula errors in google docs but not excel
My fraction rounding formula works in excel, but not in google docs.
=IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),16)=0),TEXT(R2,"#"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),8)=0),TEXT(R2,"# 0/2"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),4)=0),TEXT(R2,"# 0/4"),IF((MOD(INT((16*((+R2-INT(R2))+0.0312))),2)=0),TEXT(R2,"# 0/8"),TEXT(R2,"# 0/16")))))
It’s supposed to round numbers to the nearest 1/16th, but it returns “error: Unknown format text: # 0/16”
Does anyone have any ideas why google docs doesn't understand?
-----------------------------------------
following is a solution to the best of my understanding what user theDante is intending to do ... I have however generalized the formula byond just the 1/16 rounding