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