Expert: Bill - 1/31/2008

Question
Hi Bill,

I have already asked a question to Stuart Resnik and he has answered but he has told me what I already know and am stuck at.   Maybe I didn't explain myself well enough?   Anyway the Q & A is below but I'll try and expand it a bit more.

I have a sheet of data in 5 columns.   
I have 4 graphs each graph has 3 cells to the side of it which show the Average, Max and Min.

Each week I add data to the data sheet but then have to redfine the series' of each graph, each Average, each Max and Each Min to include the new data.

Rather than having to redefine 16 seperate cells/ranges I just want to use variables (which I assume is the same as a Name?).   Then all I want to do is to change the numbers in a couple of fields which would in turn change the defined ranges e.g.

Last week the range was =Data!$A$350:$B$414
This week the range is =Data!$A$350:$B$421

I want cell A1 Named "Start" to equal =Data!$A$350
and cell A2 Named "Finish" to equal =Data!$A$414

To update the 16 ranges all I want to do is to change cell A2 to equal =Data!$A$421 then all the ranges automatically update. Is that possible?

I hope that makes sense.

Regards,

Murray

Subject   Excel Formulas
Question   I have an excel workbook with 1 sheet of data and 2 sheets of graphs that use the data.  Each week I add data to the data sheet and then edit the data ranges in the graphs to include the new data for example, =Data!$A$350:$B$414 would become =Data!$A$350:$B$421.  What I would like to do is to use a name in the data range e.g. =Data!Start:Finish then define these names somewhere else so all I have to do is enter the start and finish cells just once.

I also want to do the same kind of thing but with the average function e.g. =average(start:finish) which would give an average of all values between $A$350 and $B$414.   All I have managed to do so far is get the average of these 2 values, $A$350 and $B$414.

--------------------------------------------------------------------------------

Answer   Here's an example. On sheet "data" you have your data that drives the chart. Say it's a column graph. In range A1:A4 you have the labels for the columns, and in B1:B4 you have the values. You've created your chart, and it's called Chart1.

To work with named ranges, e.g., select A1:A4, and choose from main menu: Insert, Name, Define, colAlabels, OK. Now A1:A4 has the range name "colAlabels." Similarly, give B1:B4 the range name "colBdata."

Now go to Chart1 and select the Column series. In the formula bar for Series 1, change the formula that drive the chart to:

=SERIES(,data!colAlabels,Bdata!colBdata,1)

Now, each time the data changes on data sheet, just re-define the range names to fit the new data ranges, and the Chart will pick up the new data ranges.

As for your 2nd question... if you've given the range name "start" to the upper-left corner cell of a range, and the range name "finish" to the lower-right cell of that range, and you want a formula to return the average of the entire range, the formula is:

=AVERAGE(start:finish)  

Answer
EXCEL can create dynamic ranges which means the area the range name refers to will change based on the amount of data.  So, if you have data in A1:A10, for example, and have a range named TEST then TEST will "select" A1:A10.  Add data to A11 and to A12 and then TEST will "select" A1:A12.  It sounds like this is what you want/need.

To explain how to do this takes a good bit of typing and more than I want to type.  So, you can GOOGLE for

"Dynamic range names in EXCEL" and find a lot of detailed info on this subject.  Then your graphs can just reference range names.

Hope this helps.