Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

in Excel (2007 or newer) is there an easy way to apply any Excel formula on a set of datapoints before drawing a line/box/... on the chart, without using a helper column to do so?

(This is a generalization of the more specific question how to add a average line to the graph in excel 2007 or 2010. I actually also just want to show an average line, but I am interested in the general solution)

share|improve this question
    
General answer is probably yes. Consider using named ranges to perform your calculations and then plotting the named range directly. If you have some data or more specifics it would be easier to show a direct answer. – dav Jun 3 '12 at 3:36
    
@David: AS I understand it, using named ranges is the same as using a helper column, which is exactly what i want to avoid. – Rabarberski Jun 3 '12 at 8:43
    
That's not my understanding. I only know of three ways to apply a formula to data points: helper column, named ranges and VBA. Good luck with finding a solution. – dav Jun 3 '12 at 12:41

Dav is correct. You can add named ranges that perform functions. Named ranges don't have to refer to actual ranges on your worksheet. For instance:

Chart

The redline is actual data. The blue line is a named range:

Named Range

That range performs a (very simple) function on a range. Its result is an array the same size as the source range. If you do something like SUM(Range) it returns a single value and, thus, would only chart a single value. So long as you stick to functions that return arrays, it should work fine. I don't know what kind of operation you want to do, though, so it's possible that it won't work for whatever reason.

The only other oddity is when you go to add the series to the chart. You have to add the workbook name if it's a workbook-level name or the sheet name if it's local. That should update as you change workbook / worksheet names, though, so you can set it and forget it. However, if you ever copy that chart to some other file, you might be confused about why it's showing the wrong data until you remember that reference.

Series Definition

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .