Graphs with variable amount of data

**kenwatch** · 03-30-2010, 07:57 AM

I need to do a report and put data in a graph to show the variation. Quantity of data is different every day and I have to change the data range manually every day.
Series for X values can be one day 500, another day 1000 and so on.

Anybody knows how to make the graph to be generated so that it takes the right amount of data?
Thank you!

**Dav** · 03-30-2010, 08:08 AM

You need to create dynamic named ranges and plot these on the graph. There are various formulas to define the ranges, but hopefully this will point you in the right direction

http://office.microsoft.com/en-us/ex...098011033.aspx

Regards

Dav

**teylyn** · 03-30-2010, 08:51 AM

check out http://peltiertech.com/Excel/Charts/Dynamics.html

**kenwatch** · 03-30-2010, 09:04 AM

Thank you,

I try it following the instructions from there, but it doesn't work. I am using Excel 2007 and here is what I am doing:

My chart shows for Series values: =dataLH!$F$3:$F$300

I try to modify it by keeping dataLH! and then adding the new formula for range:
OFFSET(dataLH!$F$1,1,0,COUNTF($F:$F)-1)
So, my new series values line will be:
=dataLH!OFFSET(dataLH!$F$1,1,0,COUNTF($F:$F)-1)

What mistake do I make?
Thank you,

**Dav** · 03-30-2010, 09:22 AM

you have to create named ranges and then plot the names range son the chart. Why not attach the worksheet, and then it can be ammended for you

your new series will be nameofworkbook!nameofnamedrange, where named range is OFFSET(dataLH!$F$1,0,0,1,COUNTF($F:$F)-1)

although as we can not see the spreadsheet this is a guess

regards

dav

**kenwatch** · 03-30-2010, 10:34 AM

Unfortunately, I am not very good in Excel. I tried the links but I could not succeed. I will need your help.
I do not know how to attach the file to the message. In your home page it is written that you have to allow me to upload the file. I have a 21kb excel file (Name: Exercise1.xlsx).

**teylyn** · 03-30-2010, 04:48 PM

Kenwatch,

the offset formula goes into the definition of a range name. You then use that range name in your series definition.

This tutorial is really about as easy as it gets
http://peltiertech.com/Excel/Charts/...umnChart1.html

did you follow it step by step? "I did no succeed" does not help identifying what the problemn is.
You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.