Excel Dynamic Chart #10: OFFSET Function Dynamic Range

Howdy All!

I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.

The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).

However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.

Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?

I've tried using trend lines, but they don't actually average...so they're not very helpful.

Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...

Thanks for the help.

Please help!

(I've tried searching the board, can't quite figure out an answer to my question.)

I have a table with columns representing data I'd like to chart, but I want to chart only as many data values as I have. I've already calculated a range name, num_schools, that gives the number of data points I'd like to chart, and identified the point (BASISNODE) I'll start all offsets from.

In the Edit series dialog, "Series values" box, I'm entering:

=OFFSET(BASISNODE,1,8,num_schools,1)

Excel tells me this is not a valid formula, but then when it sends me back to edit, it highlights exactly the data I want to chart.

I have a bad feeling I'd being extremely dense about this, but I can't figure it out.

Many thanks!

Hello,
I need to be able to select a dynamic range from a group of data that I have. The data are arranged in columns with 38 rows per data entry. I want to be able to use the last 400 columns to do calculations i.e. a 400 column x 38 row block of data, with the rightmost column being the latest entered data.

I have previously attempted to do this with the OFFSET function and inserting the formula into a named range to make referring to it easy. But I seem to be getting a strange problem when I tried using the formula on subsequent groups of data (I have 11 'groups' of data in this column arrangement). The data has a few rows of pre-loaded cells at the top which make it impossible for me to use the top cell as a reference point as the rightmost has no actual data. To get around this i've used OFFSET to look at the bottom datum point (38th row), continue to the right until it finds the end, then count back 400 rows. (at least that's my interpretation of offset). The problem that I get with this is that when I transfer my formula to another group and redefine parts of it as each is a little different the range that it gives me then has a few extra columns on the right where it seems to have overshot and then counted back. It shouldn't do this as there are only blank cells right of my data on the row it uses. The problem seems to compund itself and become worse with each group of data, giving me something like 132 extra columns on my last group.

To me OFFSET seems to be unstable, giving me a different result when there is nothing wrong in the entered formula.
Here's my formula in case anyone manages to see something that might cause this issue:

=OFFSET(Sheet1!$I$49,0,COUNTA(Sheet1!$49:$49)-1,-38,-400)

(If there's an error with 'Sheet1!' ignore it..I just replaced the name of my actual sheet with that)
- The first bottom datum point is in I49 for this particular group



Any suggestions on how to obtain a dynamic range without offset are welcome. I also have a problem where some of the groups have a smaller data range than others, so the '-400' causes me to get an error as it tries to get columns before A. to fix this I reduce the -400 to -200 etc, but i'll have to change that again later as the data range becomes larger to incorporate 400 entries. It has been suggested that an 'error handler' type thing be used to instruct excel what to do when it encounters this particular problem. If anyone could advise me on that it would be appreciated.

i currently have multiple dynamic named ranges in my file all defined by the following formula (or similar to):

=OFFSET($B$4,0,0,COUNTA($B:$B),COUNTA($4:$4))

the problem as you all know is that this is a dynamic function and therefore it is impacting the speed of my workbook tremendously. through searching other posts i have found out that i can define the ranges of rows and columns by using formulas similar to this:

=MATCH(REPT("z",255),Sheet1!$D:$D) (for rows)
=MATCH(REPT("z",255),Sheet2!$2:$2) (for columns)

I am just having a hard time putting those together to define a range. Can anyone out there lend some insight? Thanks for the help!

Gex

Hi All,

Multiple time reader, first time poster...

My challenge, I have a significant data set that I am creating a report on.

the challenge is I want to create an average using firstly the unique area number that only sits in the top left of each area and then average all the postcodes that are in that area.

Here's a sample of the data sheet... We will call this sheet "Data"

A B C D
Area postcode Number Number 2
20101 2401 3.58 4.54
(empty cell)2057 4.61 6.63
(empty cell)2001 4.34 8.30
(empty cell)2067 3.79 6.53
(empty cell)2053 3.63 5.14
(empty cell)2024 3.45 5.29
(empty cell)2080 3.17 3.51
(empty cell)2052 2.45 3.14
(empty cell)2604 2.38 4.38
(empty cell)2310 1.87 5.66

20102 2201 4.76 7.41
(empty cell)2352 2.44 3.88
(empty cell)2445 2.51 3.18
(empty cell)2146 2.92 3.72
(empty cell)2294 3.10 4.21
(empty cell)2116 1.86 1.95
(empty cell)2212 3.07 3.79
(empty cell)2183 2.47 3.47
(empty cell)2302 2.16 2.95
(empty cell)2289 2.23 2.92
(empty cell)2132 2.10 2.80
(empty cell)2309 4.57 4.09
(empty cell)2299 2.32 2.85

So if the user in the report selects 20101 I want to then give an average of all the numbers in all the postcodes following that area.

I am unable to populate the area next to each postcode as this will impact the source data (which is a feed)

I have over 50 areas and over 700 postcodes which are going to be reported on by a state selection.

This is the sheet "Area Report"
where I have put in what the answer should be if returned, I am at a loss as to the best formula to use.

Average Number Average Number 2
20101 =3.33 =5.31
20102 =2.81 =3.63


Any suggestions would be great?

Thanks,

DG

Hi, I have a dynamic named range with

=INDIRECT('Chart Data'!$D$2):INDIRECT('Chart Data'!$D$3)Where the cells D2 and D3 contain a cell reference in each (which is dependant on a date which changes).

When adding into a chart it does not work. I understand this is because you cannot use the indirect () function for dynamic named range charts. Is there a better way to do this using OFFSET?

Thanks

Hi all,

I have set up a named range as such: (name = "blah1")
=OFFSET('F_5050 FP'!D1223,'F_5050 FP'!$B$2-1,0,'F_5050 FP'!$H$1,1)

And it works, it selects the right range of values (a column of numbers).

Then I create a new line chart, edit series, set series values to:
='F_5050 FP'!blah1

(which is a range, right, so should work?)

And I just get "a formula in this worksheet contains one or more invalid references"

Thanks in advance

I need to add a month to a chart every month without using the offset in excel 2007. I am willing to use VBA, I just do not know the code. What I have is data from Dec-2010 through OCT-2012 (forecasted). Next month I need to add through Nov-2012. My cell in G37 states: =IF(TODAY()>G20,DATE(2012,11,1)," ") So I would like my chart to look through I14:I36, next month I need it to look through I14:I37. Any ideas? I can't use offset, it will not work in this case.

Thank You
Tony

I have set up a 2 y-axis & 3 series Line chart with the following offset formulas:

AADataLabels=OFFSET(SLBR!$A$2,,,COUNTA(SLBR!$A$2:$A$2174)+BRCht!$A$3,1)

AAvg=OFFSET(AAdataLabels,0,15)
AATotal=OFFSET(AAdataLabels,0,14)
AAcntr=OFFSET(AAdataLabels,0,5)

How do I rewrite AADataLabels so it links to a scroll bar that's linked in BrCht!$B$35 and period cell B36

I tried =OFFSET(SLBR!$A$2:$A$2174),BRCht!$b$35,1,BRCht!$B$36,1)

Hi all,
Does anyone know how I can set an EXCEL chart data range
to automatically adjust to the number of rows in my data,
which both increases and decreases over time. I can use
the COUNT function to determine the current number of
rows, but I don't know if I am allowed formulas in a
chart's data range.

thanks in advance.