Archive for the ‘Functions’ Category

How to Use ISERROR Function in Excel

Posted on April 3rd, 2011 by Joseph Harris

The ISERROR function is a versatile one that is very useful in testing an outcome of a formula so that you can account for any errors, more on that later…First, lets learn the syntax: =iserror(A1) A1 is the cell you want to test for an error. If A1 contains #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME!, or #NULL!, [...]

Remove the ‘ Apostrophe from Excel Cells

Posted on April 1st, 2009 by Joseph Harris

Many applications that export to Excel, export the data with the apostrophe character at the front of the string. One way of removing this character is to use the clean() function: If in cell A1 you have: ‘output Then in cell B1 type =clean(A1). The result will be: output Joseph Harris Excel Help Bookmark It

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

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 [...]

Counting Visible Rows After Autofilter in Excel

Posted on March 2nd, 2009 by Joseph Harris

Typically when you use autofilter, the task bar at the bottom of your Excel workbook will say: “5 of 10 records found”. The five is your visible rows. In some situations the records found indicator will not be displayed on the indicator. This can happen when there are many complex formulas in your filtered results. [...]

Using Right() and Left() Functions to Remove Text from a String – Excel

Posted on February 19th, 2009 by Joseph Harris

Today I came across a problem I see at least once or twice a week. I wanted to perform some analysis using an employee number. The output from one of my systems included the employee number with GLK_RDT at the beginning of the number. Since I needed the numbers alone, I needed to remove the annoying 7 [...]