Conditional Formatting in Google Docs

by on Jun.05, 2012, under technology

Recently, I was working on a project which involved generating a spreadsheet of a bunch of changes I was making to some information.  As part of this, I wanted to color code the rows of the spreadsheet: green would mean OK, yellow would mean a change, and red would mean a problem.  I could do all this formatting manually… but I decided to find a way to make Excel do it for me.  Enter conditional formatting!

Conditional formatting in Excel is very, very robust.  For this project, I needed to change a row’s background color and font color based on three possibilities:

  1. If Column E has any text, the row should be red.
  2. If Column A and Column D contain different values (case-insensitive), the row should be yellow.
  3. Otherwise, the row should be green.

Unfortunately for me, Excel’s conditional formatting works on a per-cell basis.  However, Excel does provide the ability to choose conditional formats based on a formula!  After some fiddling with Excel functions, I was able to generate formulas to check different cells in the row, then apply the conditional formatting based on that.  I ended up with these three formulas, executed in this order, and corresponding with the above three conditions:

  1. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))>0,ROW()>1)
  2. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))<>CELL(“contents”, INDIRECT(CONCATENATE(“D”, ROW()))), ROW()>1)
  3. =AND(CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))=0, CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))>0)

Wow!  First, notice all of these start with an AND function.  AND evaluates the conditions given, performs a logical AND on all of them, and returns a boolean true or false based on that.  Explanation of the conditions:

  1. Two conditions:
    • CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))>0 checks for text in Column E. If there is nothing in the cell, the value is 0.
    • ROW()>1 checks if this cell is NOT in the first row.  The first row is my header row, and I don’t need that one color coded.
  2. Two conditions:
    • CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))<>CELL(“contents”, INDIRECT(CONCATENATE(“D”, ROW()))) checks if the contents of the cell in Column A and the contents of the cell in Column D are different. The <> operator is “not equals”, and Excel seems to automatically do a case-insensitive comparison.
    • ROW()>1 checks if this cell is NOT in the first row.
  3. Two conditions:
    • CELL(“contents”, INDIRECT(CONCATENATE(“E”, ROW())))=0 ensures that Column E is empty. (Again, the value is 0 if there is nothing in the cell)
    • CELL(“contents”, INDIRECT(CONCATENATE(“A”, ROW())))>0 ensures that there is some value in Column A. If there’s no value in Column A, there’s no point in any highlighting.

Alright… So, with all those conditions set up, text is automatically highlighted based on what values are entered. This made editing the spreadsheet really easy: simply type in values, and the highlighting is automatic. If I go back and change something later, the highlighting changes without me needing to do anything. (continue reading…)

7 Comments :, more...

How do you Describe Security?

by on May.29, 2012, under Uncategorized

Every now and then, working in an IT position, you get asked questions that are… interesting.  Sometimes, these are questions that, while obviously simple to the experienced techie, really are things that take learning.  Tonight, however, I encountered the other kind: the kind you just don’t know how to answer.

“Are these computers secure? I’m going to be putting in my credit card information, so I want to make sure these computers are secure.”

How do I even begin to answer this question?  After the customer left, my coworker and I had a small laugh at this question, because the answer I eventually gave this person was a simple “yes”.

My coworker pointed out that I could have given this person any answer — as long as it had a few buzzwords, it would be believable.  ”Oh yeah, the memory addresses of the internet piplines sync well with Adobe Connect, so its definitely secure.”

My original answer was one about the security of a wired connection versus that of a wireless, pointing out that these computers have wired connection, so yes, they are secure.  This only gained a quizzical look from the uninitiated.

My next answer was going to be a note about SSL.  Perhaps a small explanation about how security is up to the place you are entering your credit card information, with a note that you should always check for “HTTPS” when entering credit card information.  However, if the wired vs. wireless explanation was confusing, this would have done nothing to clear up the situation.

So, instead of attempting to explain security, or gain further insight into what this customer was actually asking, I simply gave the answer “yes”.  It might seem like a cop-out, but it was the only answer I could give within her frame of knowledge.

I leave you with this video of Richard Feynman attempting to explain how magnets work, and instead explaining why he can’t answer “Why” questions:

Leave a Comment more...

Are Penny Auctions a Scam?

by on Jul.17, 2011, under technology, thoughts

If you haven’t heard, someone was lucky enough to buy a Chevy Camero for only $5.28.  Now, if you actually read the article, you’ll notice a very important detail: this car was bought off a “penny auction” site.  These sites, like, are becoming more and more popular.  Recently, I’ve had a couple of my friends ask me my opinion on these sites: are they a scam?  It certainly sounds too good to be true.  But, I will give this sites credit: real people do win real items from these sites.  In this sense, these sites are not a scam.

So, how exactly do these sites operate?  How can anyone run a profitable business by selling $1,000 products for about $20?  The answer isn’t generous owners.  While we might like to think that there are people that generous in this world, it’s simply not a sustainable business model.  Click through to the rest of the post, and I’ll do my best to explain how these sites work, and why you shouldn’t (or maybe should) waste your time.
(continue reading…)

Leave a Comment :, , , more...

Obligatory Google+ Post

by on Jul.03, 2011, under technology, thoughts

Well, here it is — my actual post about Google+.

For those of you who still haven’t heard, the bird is the word Google+ is Google’s new social networking experiment.  Their response to Facebook, if you will.  Google has more-or-less tried this before, with Buzz, but Buzz never really took off.  At this point, it’s difficult for me to tell what Google’s strategy is for how Google+ and Buzz will coexist.  Right now, they seem to be completely separate, yet strangely intertwined.  I have a feeling that as Google+ develops, it will eventually completely replace Buzz.

I hope this is helpful.  Unfortunately, I’ve left out screenshots for now.  I’ll try to get some in later this week to better illustrate what I’m talking about. Added screenshots July 4 @ 11:50 PM.

Now that you know what Google+ is, click through to read an overview of my view of the evolution of Google+, a review of the UI, and my general thoughts on things.
(continue reading…)

3 Comments :, , , , , , , , , , more...

Opening the Floodgates (Google+)

by on Jun.30, 2011, under Uncategorized

Alright… I’m a nerd. Here’s my obligatory Google+ post:
I just got an invite! Still playing around with it, so I’ll try to post more thoughts later.

For now, if you want an invite, post your e-mail address and I’ll see what I can do 😉

6 Comments :, , , , more...

How I Wrote My First Software Crack

by on Jun.22, 2011, under technology

Before I really get into this article, I need to place a couple notes here:

  1. This is a semi-technical post.  It assumes some knowledge of programming and how software works, but I’ll try to keep it as simple as possible.
  2. I’ve done my best to not give away what application I’m writing about.  This is on purpose.  While this is an interesting look into hacking software, getting around DRM is, to the best of my knowledge, illegal.  This article is actually about reverse-engineering, not cracking software.
  3. If the author of this application finds this blog post and thinks I’ve given away too much, they should please e-mail me and I’ll take it down.  If you think this article is about your application and it is not, however, I will not remove the article.
  4. If you like software, buy it!  Seriously… software developers need to eat, too!

Now, let’s get into the bread and butter of this article!

(continue reading…)

1 Comment :, more...

The Potential of .anything

by on Jun.19, 2011, under thoughts, Uncategorized

Today, ICANN announced that they will allow companies to create their own top-level domains (TLDs), for a price, of course.  It’s a hefty fee (~$185,000), but every reporter has brought up a couple excellent examples.  .bank, for example.  I assume that whoever creates these TLDs would also be able to control who gets domains.  That would make it possible to ensure that only legitimate banks can have a .bank address.  The two other examples I’ve seen repeatedly are .vegas and .canon.

This brings up an interesting opportunity for some new domain uses we’ve never seen before.  Imagine, for example, that Canon bought .canon.  Sure, they would probably setup and, but what if we thought outside the box a bit more?  What could we do with an unlimited supply of .canon domains?

Here’s what I imagine: (serial number).canon.  What if instead of stumbling though a support site to find support for your product, you could just type in the serial number, followed by .(brand), and be given support information?  This is something we’ve never been able to do with traditional TLDs, buy company-oriented TLDs would make this simple.

What about tracking numbers?  Google makes it simple to get tracking information from almost any shipping company (just search Google for the tracking number and you’re given a link), but custom TLDs could make this even easier.  (tracking number).ups should take me right to the page to figure out where that book I ordered is.

How about finding out what’s around you?  It’d be neat to see some company snatch .gps, then allow some standard coordinate format to give a map and perhaps some useful information about what’s nearby.  Perhaps .gps could also allow city names.

Maybe you want to know the weather.  (City name).weather should be able to take you right to useful information.

Opening up the allowed TLDs allows a world of possibilities.  I’ve just listed a couple simple examples here, but I’m sure folks that are much more creative than I could come up with even wilder examples.  What would you like to see done with this new world of domain names?

Leave a Comment :, , , more...

Why OnLive is Ultimately a Bad Idea

by on Dec.22, 2010, under thoughts

Perhaps some of my readers have heard of OnLive.  If not, here’s a quick rundown: signup for an OnLive account, and start purchasing games through OnLive.  Now, when you play those games, they don’t run on your computer.  Instead, they run on one of OnLive’s servers, and the video (HD video, that is) is streamed to your computer.  The idea is mind-blowing.  It means that you don’t need to worry about your computer hardware being old — you can always play the latest games, because OnLive keeps their servers up-to-date.

Now, people who know a bit about the Internet and gaming might immediately claim that this is a bad idea because the lag would be HUGE.  Well, I have no idea how they did it, but OnLive has created a system where lag simply isn’t an issue.  Granted, it requires a good high-speed Internet connection, but as this becomes more ubiquitous, OnLive will be accessible to more consumers.  Another neat thing about OnLive is that you can play a free demo of most of their games.  Essentially, they give you (I think) 15 minute access to the game, so you can start to try it out, but your time will be up just as you get interested.  Finally, this should theoretically allow completely cross-platform gaming.  There is no Linux or Mac client currently, but as soon as one is created, you should be able to play any game in their catalog on any computer, with no extra work for the game creators.

Now, here’s what I think is wrong with OnLive: it’s DRM to the extreme.  One of the main concerns of DRM is what happens when the servers go down?  When the activation servers for popular games are finally taken offline, what will happen to people who own the game?  They simply won’t be able to install it anymore, and, therefore, the game will be useless.  Well, what happens when OnLive finally goes down (and I do believe that it will, sooner or later, die)?  All those games you paid for will be gone.  You’re not buying these games, you’re renting them.  And when OnLive is charging more for the game than it costs to OWN the game (compare Assassin’s Creed II — vs., it doesn’t seem right that you don’t actually OWN the game.?

If OnLive can come up with an agreement with the creators of the game to give you a downloadable copy of the game IN ADDITION to the OnLive version, the price premium will be warranted (and they could even charge $5-$10 more for the game).  Until that time, OnLive just isn’t worth it.

What are your thoughts on OnLive?

4 Comments :, more...

Decoding Comcast’s Caller ID!

by on Oct.14, 2010, under Uncategorized

It’s been a while since I’ve written about this, but there’s been a flurry of activity recently, and it’s time for me to post about this!

Comcast’s caller ID encryption has been broken!  It’s actually a very simple AES256-CTS encryption.  The tricky part is getting the key and the caller ID information.  Getting the encrypted information is actually very simple: just connect to the right XMPP server.  Getting the key, however, requires decrypting a flash file.

Anyway, with the help of a guy who calls himself Henry (if you want any more credit/a link to your site/whatever, let me know!), I’ve gotten a small python script working which will connect to the correct server and listen for the encrypted info.  When it’s found, the information will be decrypted, and displayed in the terminal.  Basically, we can get the unencrypted information!  From here, it should be trivial to do whatever we want with it!  I plan on attempting to write a Network Caller ID server with the current code as the base, but I’m not sure how much time I’ll really have to work on that/how far I’ll get. (continue reading…)

12 Comments more...

Why Net Neutrality is Important

by on Aug.20, 2010, under life, technology, thoughts

I can’t believe I haven’t written a blog post about Net Neutrality.  For those of you who know me personally, you should know that I’m a very strong supporter of Net Neutrality, and believe it’s very important that we make sure the Internet is kept neutral.  However, for whatever reason, there are a LOT of misconceptions about Net Neutrality, and what exactly it entails.  Since Net Neutrality in reaction to Comcast has once again come up in the news recently, I figured I should write a blog post about the subject.

First, let’s go into some background on the Comcast case.  A year or so ago, Comcast decided that its network was being congested by too much P2P traffic; namely, traffic from the BitTorrent P2P protocol.  So, they decided that they would clear their network of this congestion by carefully denying BitTorrent connections.  They did this by looking into the traffic that BitTorrent was sending over the network, and sending back false information so that connections to peers would fail.  The actual details of how this was done is outside the point of this post.

After some outrage from Comcast customers who used BitTorrent, the FCC decided it would step in and tell Comcast to stop or suffer consequences.  As soon as this happened, there was some question about whether or not the FCC actually had the power to do this.  But, the case went to court and a judge decided that the FCC did have the power to do this, and that Comcast had to stop denying BitTorrent connections in this way.  This was a major win for users of the Internet: the court decision basically meant that your ISP can’t deny you from accessing information on the Internet. (continue reading…)

1 Comment :, , , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Visit our friends!

A few highly recommended friends...