Spy Journal 3.0 Filtering the web for you

One of our readers Daniel sent in an email a while back (actually 6 months ago sorry Daniel) about a detailed post he had written about how to Use Excel to find the Best Used Car Deals.

I wont repeat the whole article, but get you to go over to his site, read it and try it out and comment on how you found it to work for you. I will post a couple of snippets here for you as teasers.

A used car can have any combination year and mileage. Both of these factors contribute to depreciation and make it very difficult to price-compare cars. Should you buy a 2011 model with 10,000 miles, a 2005 with 50,000 or a 2007 with 15,000 on the odometer? This guide shows you how to use Excel to find the “sweet spot” in the used car market for any given model, where depreciation and mileage come together to give you the best deal.

You’ll learn how to quickly scrape data from hundreds of used car listings on the Web, plot it in Excel and discover which model years are the best deal for a given mileage count. Instead of comparing between a handful of models, you’ll be able to see the whole market on your computer screen. This strategy also reveals which cars are the most overpriced and exposes information about how dealers price cars. This project will take an hour or two to complete, but save you thousands of dollars on a used car purchase.

Read the rest of his very detailed article with links, screenshots, a sample spreadsheet to download and try it yourself. Thanks Daniel.

The power of the human spirit never ceases to amaze me. But this last weekend I was treated to some pretty unusual displays of just how incredible humans can actually be.

I attended, photographed and even competed in an ultra running event, the 24 and 48 hour ultra event at http://geoffsruns.com/. I saw people run 280+km (175 miles). I saw records broken, i saw pain and suffering and individual determination and grit, and most importantly I saw the incredible power of encouragement. I saw friends and relatives camp over night in cold uncomfortable conditions in order to be able to get out on the track and walk a couple of laps with competitors and mentally assist them to over come the physical demands they were placing on their systems. i witnessed first hand the incredible power of transformation of a persons face from resigned grit and determination (or even pain) to sheer joy through a simple spoken word – a “you can do it”. I saw the camaraderie of those who shared the same trials, and for a brief 1.5 hours i was amongst it. As I churned out laps (more of a slow grind then a churn) I was guaranteed of numerous encouraging remarks from complete strangers on the sidelines, a “well done Tim” and “you are looking great” from other competitors who went flying past me, and the cheers from the crowd around the timing tent.

And at the end they were no longer strangers. Many of these runners I have seen and photographed (and run with) before, but as Tamyka put it – “you got to be friends with someone you shared the same physical space with for 48 hours!”

The ultra running community is a small tight knit friendly group of positive goal orientated cheerful people. I’m proud to be associated with such amazing human beings.

Here are some pics. More at http://dreamsportphotography.com/results/2012-caboolture-24-48-hour-race

Here are some videos I made also

I was interviewed yesterday by 3CR breakfast radio show in their technology spot for this morning.

I was talking about the role social media, primarily twitter, has in TV shows as a engagement method for fans and watchers, as well as the opportunity for the show broadcasters to be involved with and engage their audiences through these channels.

Here is the audio recording of the show.

Download this Media File - (Right Click)

The named range feature in Excel has been there for a long time. However the interface for the Name Manager changed in Excel 2007 and Excel 2010.

This article explains how to use the new name manager.

Firs of all let me explain what the Name Manger is and what it does.

From the Excel Help:

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference, constant, formula or table. each of which may be difficult to comprehend at first glance. The following information shows common examples of names and how they can improve clarity and understanding.

I use Range Names to clearly define parts of the spreadsheet data tables and lists that i want to refer to in formulas with a easily understood name rather than the actual sheet and cell reference. This makes it much easier to audit a spreadsheet for accuracy.

To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager. There is a keyboard short cut of CTRL + F3.

Here is one from a spread sheet tool I am working on.

In this case most of the named ranges are using the Index function in order to be a dynamically expanding range. (see previous articles and links to using INDEX to create dynamic range names)

Adding a new range is very simple, click New and type the range name and then use the range selector to go and select the range. But there are some shortcuts.

You will note from my example above that there is a very structured range name convention that I am using.

I use a prefix to define the range types as follows:

• rng_ for a single cell range
• lst_ for a list – usually a vertical column that needs to expand as new items are added to it. I use this for dropdown lists.
• tbl_ for a multi column and row table – usually for a lookup table that returns results used to search a data table
• data_ for a large data table that needs to be used in the spreadsheet – eg raw data.
• col_ for a column in a data range
• row_ for a row in a data range

The next thing I do is consider what my convention will be. In this case i have several defined sheets with similar information each of which is an In or Out. So the convention identifies the type of sheet, the data type and the In or Out nature of the data. building a convention like this and documenting it allows yourself and any other developers to easily understand from the names used in a formula what data is being accessed.

E.g. here is a formula from this file. =INDEX(col_RDSlot1Out,MATCH($W2,col_RUnique,0))&"/"&INDEX(col_RDCh1Out,MATCH($W2,col_RUnique,0)). I can look at this and understand it almost instantly.

The next thing i do after working out my convention is to work out all the range names i will need. I then type them in cells. This can be done quickly by copying and pasting or filling down and editing as required.

The reason for doing this is because Excel is very smart – if you select any of these cells and open the Name Manager and hit New (CTRL+F3, ALT+N) it will prefill the range name with the text from the cell. This can make it much faster to create a large number of range names. In addition if you are using a standard formula with just minor changes for consecutive ranges then you can copy and paste the same formula into the range name and then just edit it. Note you cannot use the left and right arrow keys while editing the range name.

I hope these hints will assist you in using the Name Manager more efficiently.