You may remember the Bang-Bang Servo diet (that I mistakenly called the Big Bang Servo diet). Well, it’s a hit. I was setting up a spreadsheet for someone this week and I noticed that I didn’t make the chart ranges dynamic. That is, it always charts 90 days because that’s how I happened to set it up the first time. So I fixed it.
Normally, to make dynamic ranges I use a named formula using OFFSET and COUNTA. It might look like this:
And that will certainly work in this case. I’m not a fan of arbitrary row counts, like the A1000 reference above, but I’m usually too lazy to change it. For some reason, I was trying to do my counting on column C, which contains a bunch of NAs. Before I realized that I could count on column A, it dawned on me that I already had the starting and ending dates in the user input section. So I used those, of course.
got the job done and was cleaner. Now I can use those dynamic ranges in my series ranges.
You can download the updated WeightChart.xls.zip. There seems to be a lot of names floating around for this: Steve Ward diet, bang bang servo diet, bang bang diet, line diet, over under diet, and overy-undery diet (my favorite). Happy dieting.
The way I set up my input tables in 2003 is as follows:
Assumptions:
– Column headers on row 1
– Content every row after row 1
– Column 1 is the identifier column, and is non-blank.
ref_RowCount =COUNTA(Sheet1!$A:$A) – 1
ref_Identifier =OFFSET(Sheet1!$A$1, 1, 0, ref_RowCount)
ref_SecondColumn =OFFSET(Sheet1!$B$1, 1, 0, ref_RowCount)
ref_ThirdColumn =OFFSET(Sheet1!$C$1, 1, 0, ref_RowCount)
…
It allows rows to be deleted, columns to be re-ordered.
Of course, this all gets a lot easier by using 2007 Tables.
Dick: Irrespective of how one computes the dynamic range, base only the 1st range on the calculated range. For subsequent ranges, use =OFFSET(FirstRange,0,{n}) This way, if there is a change (correction / enhancement) in the computation of the dynamic range, subsequent names will update automatically.
For an example, see Dynamic Range (http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html). The first example shows how the x range is based on the y range. The second illustrates the other way around.
Rob –
You don’t need to suffer Excel 2007 for easier dynamic ranges. Excel 2003 introduced Lists, the grandpappy of 2007’s Tables.
In fact, Lists in 2003 was the single feature that convinced me to upgrade myself from Office 2000 to Office 2003, bypassing 2002 entirely.
The thing I don’t like about lists is that if I unlock the input cells (leaving the formula cells locked) and protect the sheet, I lose the list functionality of being able to add rows. At least I haven’t been able to find any way. Does anybody have a way to deal with this?
Jon: you’re right, of course. I actually meant 2002 (which I run at work). I run 2003 and 2007 at home.
Awesome Tushar. Thanks for that.
Normally, to make dynamic ranges you should use Index/Counta
=$A$1:Index($A:$A,Counta($A:$A))