Spy Journal 3.0 Filtering the web for you

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}))))))

I 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!

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.

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.

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.

While looking at previous posts to choose a different function for this weeks post I noticed that I have never written a post specifically about the most commonly used function in Excel, the SUM function.

So today’s post remedies that. Note this post is based on Excel 2010. Most of this is still relevant in Excel 2003 and Excel 2007 is largely the same as 2010. The main difference is the ribbon references in Excel 2007/2010 vs the toolbar in 2003.

First the Official Excel Help information.

Description

The SUM function adds all the numbers that you specify as arguments. Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.

Syntax

SUM(number1,[number2],...])
The SUM function syntax has the following arguments:
number1    Required. The first number argument that you want to add.
number2,,...    Optional. Number arguments 2 to 255 that you want to add.

Remarks

If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored.
If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.

Possibly one of the most powerful easy to use formulas in Excel is the SUMIF formula.

It is very easy to use once you get your head around its syntax and it is extremely useful in all sorts of scenarios.

Here is the Microsoft Excel Explanation.

You use the SUMIF function to sum the values in a range (Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5.

Syntax SUMIF(range, criteria, [sum_range])

I will explain that below more simply.

First let me explain some common scenarios where you might use SUMIF. Hopefully from there you can envisage similar scenarios for your own circumstances.

You have a list of names and values – maybe pays or scores or totals of times you beat them in darts matches or numbers of volunteers in each division or county. When the names appear more than once – e.g. you might have several different staff categories in each store across each state as per the example below.

Lets say you want to sum the total number of Part Time Employees in South Australia across all stores.

I am going to show you a summary table below this one that provides all the results for each staff category for each State and then explain how this was done using one simple formula. An understanding of ABSOLUTE and RELATIVE references is important.