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

I am creating a line graph in Excel 2007. What I would like to achieve is to specify a formula in the chart data range.

What I have now in the chart data range is:

=SHEET1!$B$1:$W$1;SHEET1!$B$7:$W$7 

This simply creates a line graph using the B1,C1,D1,... and B7,C7,D7... values.

However, I would like to manipulate the second data ($B$7:$W$7) by dividing it another row and then use the result in the graph. So the result should be something like:

=SHEET1!$B$1:$W$1;SHEET1!($B$7:$W$7/$B$8:$W$8)

This formula returns error. I need B7 divided by B8, then C7 divided by C8 and so on. And the calculated result should be used in the graph.

PS: I know that I can easily create a new row for my calculation and create a graph with it. But I was wondering if that calculation can be made on the fly when creating the graph.

Thanks

share|improve this question
    
This most likely isn't going to work the way you want it to as your range will probably evaluate to a single number. Check this out: How to Divide a Range by a Range – rishimaharaj May 29 '12 at 12:41
up vote 0 down vote accepted

You can use a Named Range to accomplish this. Create a Named Range that has the calculation you want to chart (e.g. named range value=Sheet1!$B$7:$W$7/Sheet1!$B$8:$W$8). Then, add this Named Range as a data series to your chart and it will automatically calc your series for you-without the need of a "cheater row".

share|improve this answer
    
Worked like a charm! Thank you – Ciguli May 30 '12 at 16:37

You must log in to answer this question.

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