• Sharebar

## How to Use Excel to buy a Second Hand Car

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.

## Using Named Ranges in Excel 2007 and 2010

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.

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.

## A practical way to use the IF function in Excel

The IF function is a very useful logical tool. I have written an explanation on the IF Function before so I wont repeat that here. There are a number of other posts also that incorporate the use of the IF Function.

What I want to do is highlight a useful way to use the IF function.

Obviously I have obfuscated the email addresses in this example for privacy reasons.

The actual IF statement is very simple. In Cell C1 I wrote =IF(B2=A2,0,1). Filling it down is as simple as double-clicking the right hand bottom corner of the cell.

Then I added a simple filter to hide the correct results and only display those with differences.

I use this technique many times a week in comparing data lists.

## Using VBA Code to Delete Cells Based on the Font Color

A while ago a reader named Jeremy sent me a request. Sorry I took so long to respond Jeremy – but I’ve finally done this and here are the results.

How can I delete an entire row based on the font color in column A?

I want do delete every row between A2 and A1000 where the font color is black or automatic. I have tried various alterations of the codes below.

Jeremy supplied several procedures, none of which worked correctly.

I have built a very simple version that does what is needed. I know that this is not good code, but it provides a good starting point. And it works. Anybody who needs to use the guts of it (the selection and deletion based on the font colour) can take that and apply it to whatever ranges they need to.

Here is the scenario.

1. You have a range of cells that have been coloured with the automatic font colour. (this could be any colour – you would just need to change the colour index in the code.)
2. You have some items that have been coloured with a different colour.
3. You need to delete the entire row of any cell that has not been coloured specially, ie all the automatic colour cells.