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
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
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 [...]
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. [...]
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 [...]
Posted on February 17th, 2009 by Joseph Harris
The #N/A error happens most often when a lookup function cannot find a result. You can manually remove the all of your #N/As or you can stop them in your original formula. A simple and effective way of trapping the #N/A errors and replacing them with whatever you would like is to use the ISNA() [...]
