Ivor O’Connor

June 1, 2013

Google Reader Officially Dead Today

Filed under: GOOGLE, GOOGLE DOCS — ioconnor @ 4:42 pm

This is the end, beautiful friend This is the end, my only friend, the end
Of our elaborate plans, the end Of everything that stands, the end No safety or surprise, the end I’ll never look into your eyes again
Can you picture what will be So limitless and free
Desperately in need of some stranger’s hand In a desperate land
Lost in a Roman wilderness of pain And all the children are insane All the children are insane Waiting for the summer rain, yeah
Kill, kill, kill, kill, kill, kill, kill — The Doors.

Well Google has now, as of today, killed its reader.  Sort of reminds me of GM killing their EV. Despite candlelight vigils.

Google is pure evil these days. Everybody needs to get off of them as quickly as possible. For many many reasons. Unfortunately many of their applications are very compelling. Like chrome, reader, docs, search, maps, and such. Feedly appears to already be better than reader. The next step for me is to get off of docs.

To do this I’ll need to move my documents somewhere that are always accessible. Something like https://www.dropbox.com/ but with more storage. BitTorrent Sync appears to be perfect. I’ve now installed it on a Windows 7 and Linux Mint 14 computer and appears to work nicely. I’ll be putting it on all my computers. The strong points of BitTorrent Sync are:

  1. Runs on Windows, Linux, and the Mac.
  2. No copy of your data on the web like with DropBox and Google Docs.
  3. No external limitations on space. DropBox only gives you a few GBs. BitTorrent Sync has no disk space limitations.
  4. Don’t need to be online to sync your data between computers on your home network. BitTorrent Sync automatically handles this.
  5. Uses the encrypted BitTorrent protocols for speed and privacy.
  6. Is totally free.

With access to your files wherever you are you can now use libre office instead of google docs. What to do for email, maps, and such will be worked on in future posts.

Moral of this post is that google is evil and you need to prepare ahead of time before they get you.

February 22, 2009

Google Spreadsheet Data Validation

Filed under: GOOGLE DOCS, Uncategorized — Tags: , — ioconnor @ 6:40 pm

It is good to see google attempting to supply data validation on the cells of a spreadsheet. See details here:
http://googledocs.blogspot.com/2009/02/data-validation-gives-you-more-control.html

However it’s only a first start. In my spreadsheet demo yesterday the input for employee tax status could not have been validated. As one person mentioned google’s text validation is more like a check off box. Either the text matches exactly one string or it does not validate. There’s nothing equivalent to a pick list. How long will it take Google to supply a pick list? Or for that matter to verify the numeric dollar amount is withing a certain range? I bet it won’t be long. The question is why did they not include these validations in their first release?

February 21, 2009

Tutorial / Example on Google vlookup Spreadsheet Doc

Google spreadsheet information is hard to find. It’s not nearly as documented as Microsoft Office or Open Office. Subscribing to their official blog at http://googledocs.blogspot.com/ is helpful. They replaced the usenet group with a forum at http://www.google.com/support/forum/p/Google+Docs?hl=en which is under the general category of google docs support at http://docs.google.com/support/. Here you’ll find descriptions like the following for vlookup:

VLOOKUP(search_criterion, array, index, sort_order)
Searches vertically with reference to adjacent cells to the right. If a specific value is contained in the first column of an array, returns the value to the same line of a specific array column named by index. Search_criterion is the exact value searched for in the first column of the array. Array is the reference, which must include at least two columns. Index is the number of the column in the array that contains the value to be returned. The first column has the number 1. Sort_order (optional) indicates whether the first column in the array is sorted in ascending order.
We’d love to learn how you’re using the VLOOKUP function. You can now show off your finished spreadsheet by editing our knol at [link] (a knol is an authoritative article about a specific topic).

Calculating federal tax withholdings is a good way of demonstrating vlookup. The documentation on federal tax for 2009 can be found at www.irs.gov/pub/irs-pdf/p15.pdf. The percentage method is described on pages 36 through 39. It’s a simple three step process. 1) Calculate withholdings by multiplying the number of them by $70.19. 2) Subtract this from the gross pay. 3) Use this number to access the appropriate table on page 38 and 39 for the federal tax. To make life simpler only the first table was used. (The table for people being paid on a weekly basis.)

Two sheets were used. The first sheet is for the employee. The second sheet contains the tables. The first sheet for completeness sake has columns “pp” for pay period and such. Needed input are Status, number of deductions, and gross pay. Under status there is ‘S’ for single, ‘M’ for married, and ‘H’ for head of household. The unnamed column to the right of these yellow input columns is for validation. If the input validates then it is assigned a ‘P’ and the calculations to the right can proceed. The table sheet is basically a copy of the information from page 38 left in basically the same format making it easy to copy.

The validation column is interesting.

=if(and(or(“s”=E11,”M”=E11,”H”=E11),and(0=F11),and(0G11)),”P”,)

Apparently case is not important in comparisons. I put literals to the left for safety so no accidental assignments could happen. The logic operators remind me of reverse polish notation on HP calculators for some reason. If implicit logic couldn’t have been used there are true() and false() functions to fall back on. If it doesn’t validate then nothing is inserted keeping the sheet uncluttered.

The gross minus deduction is simple.

=if(“P”H11,,if(0>G11-F11*70.19,0,G11-F11*70.19))

In English it reads if the input passes and the deductions are less than the gross then subtract them. Otherwise there is no Federal Tax.

Unfortunately the table lookup with vlookup is not so uncluttered. It seems either a spreadsheet looks cluttered yet full of simple logic or looks uncluttered but with cells full of pages of logic. Or maybe I don’t know the right tricks.

=if(“P”H11,,if(0>if(“M”=E11, (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,3)), (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,3))),,if(“M”=E11, (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$20:$D$26,3)), (((I11-(vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,1)))* vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,4))+ vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,3)))))

In English it starts off verifying the input passed. Then a check is made to make sure the result does not go negative. Unfortunately this duplicates a lot of code. Once this is done the employee’s status, married or not, is checked to determine which of the two weekly tables should be used. Finally the vlookups comes in to play each one looking something like this:

vlookup(I11,’Table5 – Fed Witholding’!$A$10:$D$16,3

The important thing to notice is the vlookup took only 3 arguments. The first argument is the argument to be searched for. The second describes a rectangle to be looked at. The third is the index of the column to be returned. The index starts at 1 not 0. Now you’ll notice this does not work like similarly named functions in open office and M$ office. And the documentation is wrong. An exact match is not needed.

Anyways, you can see vlookup’s usage and play with it in the google spreadsheet here:
http://spreadsheets.google.com/ccc?key=petVh_GdUZbASvuKR4dMOPA&hl=en

You’ll need to login by making yourself an account and then copy the spreadsheet to play with it in any detail. Also some of the code in this post is wrong because wordpress garbled it. See the spreadsheet to see the correct non-garbled code.

Blog at WordPress.com.