Monthly Archives: April 2009

Condensing Rows of Data Sets in Excel

In conducting research for computer science topics, I often find myself with huge CSV files that I open in Excel and try to make graphs. Often, I have way too many data points to graph. Sometimes, I do fancy things in my code to produce only a certain number of data points that are averages or maximums over a set, but other times I just want to pare down the points in Excel to make the graph.

The following shows how to use Excel formulas to get every nth value from a column.

Example:

  • Data is in columns B and C, ranging from row 4 to row 10004
  • Goal: put every 500th pair of values in Columns E and F, starting in row 4

Solution:

  1. Put the value 500 in a cell to reference later, (example: C2).
  2. In E4, put the following formula: “=OFFSET(B$4,(ROW()-4)*$C$2,0)”
  3. Select E4 and use the fill box and drag to the right to fill F4
  4. Select both E4 and F4 and use the fill box to drag down as far as you need

That’s it, you now have every 500th data point. Simply change the value in C2 to 100, to get every 100th instead, etc.

Google Chrome

A little while back I wrote a blog post about how I wasn’t very excited about the news of Google’s new browser, Chrome. My feeling being that we have some pretty good browsers already, and I wished people would focus on making other improvements instead of reinventing the wheel, and trying to force all web pages to render well in yet another browser.

Now here we are a few months later, and I must say, the wheel was reinvented very well this time…And since installing Chrome, I very rarely use other browsers.

Here is what I really like about Chrome

1. Startup Speed. If I accidentally click Firefox, and start waiting, I usually click Chrome and I’m already off and into whatever page I needed long before Firefox finally opens.

2. One box for everything. I thought this would drive me crazy because I thought they would mix up addresses for searches and vice versa, but leave it to the folks and Google to get it right. I don’t think I’ve ever had a problem of them mixing it up in an unexpected way. On the other hand, now when I use Firefox, I constantly find myself putting searches in the address bar. It seems so archaic to have to have two separate boxes =).

3. Most visited startup page. I didn’t think this would really impress me that much either, but I was wrong. The nine pages it displays capture almost every page I visit on a recurring basis, and anything else starts with a search anyway.

4. Tab independence. The ability to move tabs from one browser to another or to it’s own window is a huge improvement over other browsers. Also to have other tabs unaffected when one dies is very nice.

At this point, the only reason why I use other browsers is:

1. Some pages don’t render right. These haven’t been very frequent, but it has happened.

2. Firebug.