Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

How to remove #N/A errors in Excel

Today I had an email request:

I wasn't sure how to post, or join so I'm sending my question to you this way. I cannot figure out how to remove the #N/A error in LOOKUP. If the cells are blank it returns the #N/A error - I'd like to return either a blank or 0 instead. How do I do that? Her is my formula. (I'm sure you can tell by my formula I'm a rookie at this.) Any help is appreciated. Thanks!

=SUM((LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))+(LOOKUP(C6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(D6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(E6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(F6,{1,2,3,4,5},{40,30,20,10,0}))))))

Excel-2013I have written a post on this before when I explained the ISNA function.

In this case I will break down the first component of the formula and show you how to insert it.

First of all understand that because this formula is summing components that are lookups, if any one of them returns an error than the overall sum will be an error even if the rest is not an error.

So to fix =LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}) when it displays #N/A (eg if B6 is less than 1) we would use this function:

=IF(ISNA(LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))=TRUE,0,LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))

The same concept could be used for each lookup component. In this case the formula would become very involved so there might be easier ways to do it. Sometimes breaking the individual components our into individual cells and then summing them is a way to give more visible results – and easily see which component is causing the error.

So Lois – i hope that helps – and thanks for liking us on Facebook!

jethro's picture

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.

excel_scatter_plot

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

jethro's picture

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.

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.

image

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.

image

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

image

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.

image

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.

jethro's picture

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.

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

I use it a lot to compare lists of data. today I had been sent some data in a spread sheet that contained email addresses. I had to create a mail out to those email addresses and when i did that I received a number of bounce backs for incorrect email addresses. I then received a second email from the original person containing a revised spread sheet with the corrections to email addresses. given that there were a large number of addresses and only a few changes I didn’t want to painstakingly work my way through the list looking for changes. Instead I copied the new list beside the old list and then wrote an if statement to compare the original with the revised. It is very simple and uses a 1 or 0 result. You could use anything there including TRUE or FALSE or “OK” and “ERROR” etc. Then a simple filter showed me the incorrect ones and the fixes i needed to apply in my mailing list.

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.

image

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

image

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