Tuesday, March 6, 2012
Monday, March 5, 2012
yogi_Use Split With An Array Correctly Handling Items In The First Row As Well
Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #457 www.energyefficientbuild.com
user istril said:
using split with an array not working as expected, also won't sort
here is mydata:
libj:_old_do_NOT_ 0 0 0 0 0 0 0 0
Libb:_Crown_Point 82 82 0 76 7 82 6 1266
Libc:_East_Chicag 0 0 0 0 0 0 0 0
Libd:_Gary 0 0 0 0 0 0 0 0
Libe:_Hammond 2 2 0 1 100 2 0 100
Libf:_Jasper_Coun 138 138 0 141 -3 138 11 1154
Libh:_Lake_County 11 11 0 11 0 11 0 100
Libg:_LaCrosse 2 2 0 2 0 2 0 100
Libk:_Lowell 118 120 -2 122 -4 118 10 1080
Libi:_LaPorte 2 2 0 2 0 2 0 100
Libl:_Michigan_Ci 1 1 0 1 0 1 0 100
Libm:_North_Judso 1 1 0 1 0 1 0 100
Liba:_Any_Other_L 11 11 0 14 -22 11 1 1000
Libn:_Pulaski_Cou 0 0 0 0 0 0 0 0
Libo:_Starke_Coun 2 3 -34 4 -50 2 0 100
Libq:_Westchester 3 3 0 2 50 3 0 100
Libs:_Whiting 1 1 0 1 0 1 0 100
Libp:_Wanatah 0 0 0 0 0 0 0 0
Libr:_Westville 2 2 0 2 0 2 0 100
when I enter the data, and then use
=split(A1," :")
and drag the formula down it works fine. (I am using both a space and a colon as delimiters)
BUT, this data is actually buried in a lot of other data, so I am calling it with a filter. When I use:
=arrayformula(split(filter("mydata")," :"))
it behaves oddly: for the first row of data, it will populate the first cell (the cell that actually contains the formula, so in this case it has "libj") but no other cells in that row. The data that would normally appear in that row is just gone. There also aren't any of the "--" in that row that you normally see when an arrayformula is continued. The rest of the range seems to populate as expected.
One other odd bit of behavior is that if I try and wrap this in a "sort" function, it doesn't sort at all. I have tried wrapping different chunks of the formula in sort and I can't get it to work anywhere.
anyone know why this isn't working?
------------------------------------------
there is a problem with the Google spreadsheet's SPLIT function not correctly handling the data in the first row of an array (as of this time) is a known problem ... the problem has been intimated to Google Docs Team
so I use a workaround by introducing a blank row about the first row of the real array as shown in my following solution to the problem
user istril said:
using split with an array not working as expected, also won't sort
here is mydata:
libj:_old_do_NOT_ 0 0 0 0 0 0 0 0
Libb:_Crown_Point 82 82 0 76 7 82 6 1266
Libc:_East_Chicag 0 0 0 0 0 0 0 0
Libd:_Gary 0 0 0 0 0 0 0 0
Libe:_Hammond 2 2 0 1 100 2 0 100
Libf:_Jasper_Coun 138 138 0 141 -3 138 11 1154
Libh:_Lake_County 11 11 0 11 0 11 0 100
Libg:_LaCrosse 2 2 0 2 0 2 0 100
Libk:_Lowell 118 120 -2 122 -4 118 10 1080
Libi:_LaPorte 2 2 0 2 0 2 0 100
Libl:_Michigan_Ci 1 1 0 1 0 1 0 100
Libm:_North_Judso 1 1 0 1 0 1 0 100
Liba:_Any_Other_L 11 11 0 14 -22 11 1 1000
Libn:_Pulaski_Cou 0 0 0 0 0 0 0 0
Libo:_Starke_Coun 2 3 -34 4 -50 2 0 100
Libq:_Westchester 3 3 0 2 50 3 0 100
Libs:_Whiting 1 1 0 1 0 1 0 100
Libp:_Wanatah 0 0 0 0 0 0 0 0
Libr:_Westville 2 2 0 2 0 2 0 100
when I enter the data, and then use
=split(A1," :")
and drag the formula down it works fine. (I am using both a space and a colon as delimiters)
BUT, this data is actually buried in a lot of other data, so I am calling it with a filter. When I use:
=arrayformula(split(filter("mydata")," :"))
it behaves oddly: for the first row of data, it will populate the first cell (the cell that actually contains the formula, so in this case it has "libj") but no other cells in that row. The data that would normally appear in that row is just gone. There also aren't any of the "--" in that row that you normally see when an arrayformula is continued. The rest of the range seems to populate as expected.
One other odd bit of behavior is that if I try and wrap this in a "sort" function, it doesn't sort at all. I have tried wrapping different chunks of the formula in sort and I can't get it to work anywhere.
anyone know why this isn't working?
------------------------------------------
there is a problem with the Google spreadsheet's SPLIT function not correctly handling the data in the first row of an array (as of this time) is a known problem ... the problem has been intimated to Google Docs Team
so I use a workaround by introducing a blank row about the first row of the real array as shown in my following solution to the problem
Sunday, March 4, 2012
yogi_Publish An Image Inserted In A Cell Of A Sheet
Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #456 www.energyefficientbuild.com
user BurnoutAddict said:
Image not publishing
I'm having a bit of an issue with an image not showing when publicly publishing a spreadsheet in Google Docs.
There is only 1 image in the whole of my spreadsheet document.
Heres the link to view the file as I see it (before publishing):
https://docs.google.com/spreadsheet/ccc?key=0Avs8djRUmQ2fdFFlVURlaW9oSEVFa0IzUDkwNnpEZ1E#gid=6
The image is on the "Scores and Information" sheet.
Heres the document after publishing:
https://docs.google.com/spreadsheet/pub?key=0Avs8djRUmQ2fdFFlVURlaW9oSEVFa0IzUDkwNnpEZ1E&single=true&gid=6&output=html
Note that only the Scores and Information sheet is published. I still get the same result if the whole document is published and viewable.
The image is from my Picasa Web Albums and is viewable to anyone with the link.
Anyone got any ideas as to why this is happening?
--------------------------------------------------
I tested publishing the image by itself ... and as you can see it did get published
user BurnoutAddict said:
Image not publishing
I'm having a bit of an issue with an image not showing when publicly publishing a spreadsheet in Google Docs.
There is only 1 image in the whole of my spreadsheet document.
Heres the link to view the file as I see it (before publishing):
https://docs.google.com/spreadsheet/ccc?key=0Avs8djRUmQ2fdFFlVURlaW9oSEVFa0IzUDkwNnpEZ1E#gid=6
The image is on the "Scores and Information" sheet.
Heres the document after publishing:
https://docs.google.com/spreadsheet/pub?key=0Avs8djRUmQ2fdFFlVURlaW9oSEVFa0IzUDkwNnpEZ1E&single=true&gid=6&output=html
Note that only the Scores and Information sheet is published. I still get the same result if the whole document is published and viewable.
The image is from my Picasa Web Albums and is viewable to anyone with the link.
Anyone got any ideas as to why this is happening?
--------------------------------------------------
I tested publishing the image by itself ... and as you can see it did get published
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
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
Saturday, March 3, 2012
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.
--------------------------------------------------
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.
--------------------------------------------------
Friday, March 2, 2012
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
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
Thursday, March 1, 2012
yogi_Determine Unique Count Of Entities Based On Characters A to Z Only Regardless Of Case
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
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
Subscribe to:
Posts (Atom)