Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.