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() [...]
Posted on February 16th, 2009 by Joseph Harris
One of the most time consuming parts of producing a scheduled report is updating all of the charts in the report. Many times, an analyst only wants to show the last 7 days or the last 6 months of data and not the entire data set. To achieve this, the analyst changes the the Y [...]
Posted on February 9th, 2009 by Joseph Harris
The standard vlookup function allows for one condition to be looked up. For example, if I have the data in rows 1 through 7 below, and I want to determine the favorite color of Dan, who was born in Jan and is 16 years old, the standard vlookup function will not work. The standard vlookup [...]
Posted on February 3rd, 2009 by Joseph Harris
Microsoft Excel has a limit of 3 criteria in Conditional Formatting. We can get around this with a bit of VBA code… Private Sub Worksheet_Change(ByVal Target As Range) Dim intclr As Integer If Not Intersect(Target, Range(“A1:z100″)) Is Nothing Then Select Case Target Case “ted” intclr = 6 Case 6 intclr = 12 Case 11 To [...]
Posted on February 3rd, 2009 by Joseph Harris
A chart that can be used to look up the colorindex integer to see the corresponding index color [...]
Posted on January 5th, 2009 by Joseph Harris
Quick an easy method of making your Excel graphs more appealing, by adding pictures.
