AboutDamon Ostrander Expertise I have extensive experience with VBA programming in Excel 5 through Excel 2007. I am an consultant in a small defense technology services company, and have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.
Experience I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.
I am using Microsoft Excel 2. I have completed a chart and would like to have gridlines that go through and intersect at the average. Could you help me with this?
Thanks,
Todd
Answer Hi Todd,
If I understand you correctly, you would like to have Excel automatically mark the average value of a chart data series using a line (or lines if you have a continuous curve and want both the x- and y-axis values marked). There are several ways to do this:
1. Define a separate data series for each line. You only need to specify a range that contains the two end points, and the end point values can be calculated in these cells (for example, you can use the AVERAGE formula).
2. Draw the lines using Drawing (Shape) objects. The lines could be manually drawn initially, then the lines would need to be positioned and sized using VBA code that calculates the average value. This can be made to automatically update whenever the chart is moved or resized, or the data change. I could send you an example of this latter method if you are interested.
One remaining issue is that the average will not necessarily fall exactly on one of the series' existing data points, so if you want to know the x-axis data value (or values) at which the average occurs in the chart data series you would need to interpolate between data points. This would best be done via a VBA macro. I have such a routine if you need this.
Let me know your email address it you would would like either of the items I mentioned above.
If you would like to email me your workbook my email address is below, and I could make more specific recommendations and possibly even incorporate the solution for you.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
