# Thread: Formula output to chart range (2K SR-1)

1. ## Formula output to chart range (2K SR-1)

I'm using the following formula to extract the address range from a table of values ("dailyDATA" which is sorted by date) of those line items listed within a one month period (as selected from a drop-down).

which outputs as \$B\$221:\$B\$235

The goal is to have a chart that used the output of the formula as a series range. I'm stuck on getting the chart to recognize it as such. I'm sure I'm overlooking something simple.

Any help would be appreciated.

2. ## Re: Formula output to chart range (2K SR-1)

<P ID="edit" class=small>(Edited by sdckapr on 13-Oct-03 15:07. Corrected a mistake in formula)</P>Create a named range that refers to (one line):

Then use that named range as the chart range you desire.

Explanation on OFFSET function parameters used:
Starts at dailydata (upper left cell)
goes down this many rows:
MATCH(Sheet1!\$AC\$3,Sheet1!\$B\$37:\$B\$537,0)
and over 0 columns
The range is:
rows "long"
and 1 column wide.

Steve

3. ## Re: Formula output to chart range (2K SR-1)

Thanx for your quick response but I'm still having trouble with it. Please check out the attached sample. Check out the table at W2:AF14. I can get columns AC and AD to return the correct ranges for the months being queried. The drop-down should select the correct address and put the label range in AC2 and the series range in AD2. Using the OFFSET function keeps returning a date or a #VALUE! error. This shouldn't be this hard.

4. ## Re: Formula output to chart range (2K SR-1)

That offset formula you have yields a RANGE object which refers to the range. It is an ARRAY of values, if you try to put it into one cell it will just give the first value, or if the range has no elements (like for Nov 2002) it will give #na. The formula should be in the defined name not in a cell.

It should probably be (based on your setup):
=OFFSET(dailyDATA,RowStart+1,0,RowCount,1)
since dailydata has a header in the row.

If I understand, what you want to do is:

Insert - name - define
Name: XRange
Refers to: =OFFSET(dailyDATA,RowStart+1,0,RowCount,1)
Name: YChange
Refers to: =OFFSET(dailyDATA,RowStart+1,3*'DAILY AVG'!\$Y\$1-2,RowCount,1)

Right click chart
Source data - series tab
series: Change over time
Values: =sample.xls!YChange
Category X-labels: =sample.xls!Xrange
<ok>

Now when you change date pulldown and/or other pulldown you will change the range based on the offset values.
For example Feb '03, 'Wire ties' will plot:
X range: B57:B76
Yrange: I57:I76 (Change)

If this is not what you want could you be a little more specific about what you need.

Steve

5. ## Re: Formula output to chart range (2K SR-1)

The problem is that the OFFSET function returns #VALUE!.

6. ## Re: Formula output to chart range (2K SR-1)

It is an ARRAY/range of values. If you confirm it with ctrl-shift-enter you will get rid of the #value, but it will only display the first cell in the range since you only put it into 1 cell.

I guess I am confused: What do you want to do with this range of values?

I thought you wanted that range to be used as the range in a chart?
I thought:
You want to change the date pulldown and have the chart plot those dates from column B in the "dailydata" range as the X range
And based on the other pulldown (Mud Units,Dedicated molds, or Wire ties)
Choose those same "date rows" from column C (MUD units), column F (Ded Units), or col I (wire ties) for the Y range

If that is so, follow my directions and you will plot that output onto a chart.

If you do not want to use that range in a chart, you will have to be more specific about what you want to do, as I must have really misunderstood your question. If that is the case, offset might not be want you want to use at all.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•