I am starting Graduate School next week at Saint Louis University School of Public Health. In two years I will hopefully be graduating and receiving my Master of Health Administration. Fortunately, SLU uses Google Apps for Education which means I'll be doing some posts about how to use Google Docs in the classroom from a professor's standpoint as well as how I use Google Docs to keep up. In addition, I am working for the School of Public Health on a new EMHA program. I might do a few posts about how I use Google Docs for business uses as well.
Cheers!
Wednesday, August 24, 2011
Friday, August 19, 2011
iferror(1/0) in Spreadsheets
A common desire in Spreadsheets is to actually return a blank cell depending on certain conditions. There are a few ways to do this, some that actually look like blank cells and aren't and some that are truly blank.
Consider this Forum Question. Cherub96 wants the cell to actually be blank if his source cells for his calculations are blank. Yogi's answer is perfect and is recommended. BenFDC also chimes in with a similar answer that is also recommended. Both answers give the exact same result.
The most important aspect of this post is that it is possible to return a truly blank cell in Google Spreadsheets!
The way to do this using the iferror trick. This trick is done by wrapping an error expression in iferror and omitting the second argument. An error expression is any expression that returns an error such as 1/0, sqrt(-X), NA() etc. =iferror(1/0) has become common usage and is advantageous because you can easily create an array of blank cells.
However, there remains a bug whereby if, say, A1 is formatted as Plain Text, and text is entered in that cell, then =ISBLANK( A1 ) will return FALSE (as expected), but if the entry is then deleted form A1, then =ISBLANK( A1 ) still returns FALSE. The cell is behaving as though it is housing a zero-length string, and presents another reason why we might generally resort to the LEN( ) test.
Notice, if cell A1 is formatted as Plain Text, a Text entry is made in the cell
Happy blank cell making!
Thanks to Adam, André, and Yogi for their help with contributions on this post.
Cheers!
Consider this Forum Question. Cherub96 wants the cell to actually be blank if his source cells for his calculations are blank. Yogi's answer is perfect and is recommended. BenFDC also chimes in with a similar answer that is also recommended. Both answers give the exact same result.
The most important aspect of this post is that it is possible to return a truly blank cell in Google Spreadsheets!
The way to do this using the iferror trick. This trick is done by wrapping an error expression in iferror and omitting the second argument. An error expression is any expression that returns an error such as 1/0, sqrt(-X), NA() etc. =iferror(1/0) has become common usage and is advantageous because you can easily create an array of blank cells.
However, there remains a bug whereby if, say, A1 is formatted as Plain Text, and text is entered in that cell, then =ISBLANK( A1 ) will return FALSE (as expected), but if the entry is then deleted form A1, then =ISBLANK( A1 ) still returns FALSE. The cell is behaving as though it is housing a zero-length string, and presents another reason why we might generally resort to the LEN( ) test.
Notice, if cell A1 is formatted as Plain Text, a Text entry is made in the cell
=isblank(A1) returns FALSE, =Len(A1) returns 0 as expected
When Text entry in cell A1 is deleted
=isblank(A1) returns FALSE, =Len(A1) returns 0 not as expected
However, if A1 is reformatted as Normal and
when entry in cell A1 is deleted
=isblank(A1) returns TRUE, =Len(A1) returns 0 as expected
Therefore, it appears the bug has to do with formatting a cell as Plain Text.
Happy blank cell making!
Thanks to Adam, André, and Yogi for their help with contributions on this post.
Cheers!
Wednesday, August 17, 2011
Arrayformula and Vlookup
There are a few formulas in Google Spreadsheets that do not work simply with the arrayformula. However, with some careful manipulation it seems that it can be solved.
See the question asked by srdrum when trying to consolidate two forms.
Ahab came up with the formula and I did my best to alter and explain it to srdrum. Post any questions!
Thanks Ahab for your example!
Cheers!
See the question asked by srdrum when trying to consolidate two forms.
Ahab came up with the formula and I did my best to alter and explain it to srdrum. Post any questions!
Thanks Ahab for your example!
Cheers!
Tuesday, August 9, 2011
Vlookup & Image
In the spirit of being in Vegas I decided to put together a quick example of using vlookup and image.
Basically, just type the card in the yellow box or use the drop-down and you will see a picture of the card and also the total of your cards.
Here is a quick explanation of how the formula in B3 works.
=iferror(image(vlookup(B3,Lookups!$A$1:$B$13,2,FALSE),4,115,75),"Type card above")
Starting from the middle:
=...vlookup(B3,Lookups!$A$1:$B$13,2,FALSE)...
I am looking up the value in B3 which is somewhere between 2-10 or A,K,Q,J. Once that value is exactly found in my table,Lookups!$A$1:$B$13, I want to return the value in the second column of my table that corresponds with my lookup, which is a URL to the image I want. FALSE indicates that I'm looking for an exact match. If I don't find the value then the formula errors. Note: my table is on a hidden tab.
Out further:
=...image(...,4,115,75)...
This formula grabs the image from the URL specified (which I define based on my vlookup) and then I determine custom height and width. You indicate this by typing a 4 for the second parameter. For other options see the =image() function. I then defined my height and width.
The end:
=iferror(...,"Type card above"). Iferror looks to see if anything calculated within the formula has errored. If it has then "Type a card above" is returned. If not then the calculation itself is returned. The validation on the yellow boxes insures that the formula will not error (something being typed in that isn't on my list) but I added it anyway.
Sometime in the future I'll update this to make it a usable template with suggestions on what to do based on "the book". Feel free to play around with it in the template gallery!
Cheers!
Basically, just type the card in the yellow box or use the drop-down and you will see a picture of the card and also the total of your cards.
Here is a quick explanation of how the formula in B3 works.
=iferror(image(vlookup(B3,Lookups!$A$1:$B$13,2,FALSE),4,115,75),"Type card above")
Starting from the middle:
=...vlookup(B3,Lookups!$A$1:$B$13,2,FALSE)...
I am looking up the value in B3 which is somewhere between 2-10 or A,K,Q,J. Once that value is exactly found in my table,Lookups!$A$1:$B$13, I want to return the value in the second column of my table that corresponds with my lookup, which is a URL to the image I want. FALSE indicates that I'm looking for an exact match. If I don't find the value then the formula errors. Note: my table is on a hidden tab.
Out further:
=...image(...,4,115,75)...
This formula grabs the image from the URL specified (which I define based on my vlookup) and then I determine custom height and width. You indicate this by typing a 4 for the second parameter. For other options see the =image() function. I then defined my height and width.
The end:
=iferror(...,"Type card above"). Iferror looks to see if anything calculated within the formula has errored. If it has then "Type a card above" is returned. If not then the calculation itself is returned. The validation on the yellow boxes insures that the formula will not error (something being typed in that isn't on my list) but I added it anyway.
Sometime in the future I'll update this to make it a usable template with suggestions on what to do based on "the book". Feel free to play around with it in the template gallery!
Cheers!
Wednesday, August 3, 2011
Posting on the Google Docs Help Forum
In order to get the best response on the Google Docs Help Forum it is important to follow a few guidelines.
On the Post A Question page...
What to do:
1) Assign the correct Product
2) Assign the correct Topic
3) Type a short explanation or question
What not to do:
1) Do not arbitrarily assign Products or Topics in hopes that the Top Contributors (TCs) will find the question and put it in the correct place.
2) Do not type a long winded explanation of the issue.
On the next page...
What to do:
1) Make sure your have searched for your question. Specifically searching the topic of your question. If you spend 10-15 min searching around the forum you might get an answer faster than waiting for someone to reply because the answer already exists. The is especially true for bugs. Most likely somebody has already noted it.
2) Make sure you include the following information in your post:
3) Clear your Cookies and Cache before posting. This can sometimes fix your problem and you might be asked to do this anyways.
What not to do:
1) Do not leave this section blank.
2) Do not type in all caps or violate any of online etiquette
3) Do not complain, violate terms of service, or threaten. Please understand that everybody is here to help and it is much easier to help someone who is easy to work with. You will most likely find a quicker solution to your problem.
By following these simple steps you should see a quick answer on the Google Docs Help Forum!
On the Post A Question page...
What to do:
1) Assign the correct Product
2) Assign the correct Topic
3) Type a short explanation or question
What not to do:
1) Do not arbitrarily assign Products or Topics in hopes that the Top Contributors (TCs) will find the question and put it in the correct place.
2) Do not type a long winded explanation of the issue.
On the next page...
What to do:
1) Make sure your have searched for your question. Specifically searching the topic of your question. If you spend 10-15 min searching around the forum you might get an answer faster than waiting for someone to reply because the answer already exists. The is especially true for bugs. Most likely somebody has already noted it.
2) Make sure you include the following information in your post:
- Browser & Operating System (e.g. Firefox/Mac OSX):
- Free consumer account (@gmail, @yahoo...) or Google Apps account (@yourdomain):
- Describe the steps you took that produced this error:
- Copy and paste any error messages:
This information is very important. If you don't provide it a TC is most likely going to ask for it. When answering the question about what type of account you have DO NOT actually put your email address there.
If you aren't sure about the type of account you have consider the following. If you sign in at docs.google.com you have a personal account. If you sign in somewhere else then you most likely have an Apps account.3) Clear your Cookies and Cache before posting. This can sometimes fix your problem and you might be asked to do this anyways.
What not to do:
1) Do not leave this section blank.
2) Do not type in all caps or violate any of online etiquette
3) Do not complain, violate terms of service, or threaten. Please understand that everybody is here to help and it is much easier to help someone who is easy to work with. You will most likely find a quicker solution to your problem.
By following these simple steps you should see a quick answer on the Google Docs Help Forum!
Cheers!
Subscribe to:
Posts (Atom)