Archive for March, 2009

Find the Nth Largest or Smallest Number – Excel

Posted on March 23rd, 2009 by Joseph Harris

Use the Large and Small functions is Excel to find the Nth largest number in a list. For example: =Large(A2:E2,3) will return the 3rd largest value in the data set. =Small(A2:E2, 2) will return the 2nd smallest value in the data set. Joseph Harris Excel Help Bookmark It

Delete Duplicate Rows Using Advanced Filter – Excel

Posted on March 23rd, 2009 by Joseph Harris

There are many ways to remove duplicate rows in Excel, in this articles I will give you a quick and simple method of deleting duplicates using the Advanced Filter. Assume you have data that contains multiple duplicate rows… Select all the data that you would like to test for duplicates Go to Data=>Filter=>Advanced Filter Select [...]

Vlookup the Last Occurence of an Item – Excel

Posted on March 18th, 2009 by Joseph Harris

The title of this post is slightly misleading. We will not be using vlookup to return the corresponding result of the last condition, but rather we will be mimicking vlookup to pull the last item. Consider the following data: George Smith Tom Thompson George Kenny Dan Jackson Sally Richards George Daniels A vlookup of the [...]

#DIV/0! Division by Zero Error in Excel

Posted on March 17th, 2009 by Joseph Harris

The #DIV/0! error happens when the denominator (the number on the bottom) of an equation is zero. Many times you will want to display a 0 or NULL rather than the #DIV/0! error. Below is a simple way to accomplish this. Rather than entering =A1/B1, use =if(B1=0,0,A1/B1), if you want a NULL to appear rather [...]

Multiple Condition Vlookup Function Add-in – Excel

Posted on March 16th, 2009 by Joseph Harris

The question we are asked most often by ExcelYogi readers is how can I use vlookup with multiple criteria or conditions. There are several solutions but some can be pain staking and unrealistic. Knowing that the Excel world is missing this feature, Excel Yogi has created a VLookup addin that will extend the built in [...]

Scrolling Excel Charts – Scroll Bar Graphs

Posted on March 13th, 2009 by Joseph Harris

There are many times when you may have a lot of data that does not look right when graphed all together. 300 data points is probably too many for a small chart. So what is the solution…scrolling charts! I will show you in this article how to create a scrolling chart without using dynamic ranges. [...]