trouble with OFFSET function for updating a graph

**JeepGuy** · 05-10-2011, 10:24 AM

I've been trying to figure out the offset function for the past day and seem to be getting close, but am not quite there. I have read numberous threats about the function, but am not able to fully understand what is going on or how it works.

I have a spreadsheet with data from everyday for the past three years on it. I wrote a macro where the raw data is entered and the user clicks "update graph" it is then the raw data consolidated and formatted to be entered to a graph. I am running into trouble with setting the OFFSET function so the graph updates correctly.

- I want the graph to show the last 355 days of data.
- The data for date starts in A3, number of sales starts in B3, and the average sales start in T3.
- I want the the graph to show the number of sales, average number of sales, vs the projected number of sales per day.

I am having trouble with what to set for the arguments while naming the offset formula. Any help would greatly be aprreciated!

**darkyam** · 05-10-2011, 11:28 PM

It would probably be better to use the Index function instead, since Offset is volatile. Something like: =INDEX(A:A,COUNT(A:A)-352):INDEX(A:A,MATCH(99^99,A:A)) should give you the last 355 days. This assumes there are no numbers below this chart and no numbers in rows 1 and 2. If there are numbers, such as totals, below the chart, perhaps uploading a small sample to show how many there are would help, if there are more than one. If there is only one line of totals, simply put -1 inside the final parenthesis. You will also have to adjust the 352 upward for every row with totals. If rows 1 or 2 have numbers, change the 352 to 353 or 354.

**JeepGuy** · 05-11-2011, 08:53 AM

I am not quite sure where to enter what you have told me. I have attached a sample with the same problem I am facing. One other thing I would like to point out: The person working on this before me set the average column all the way through 30000 rows... I was thinking I could possible use an if function to only fill in the average where a date exists. I am going to start working on that right now, but here is a copy of what Ive got.