• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Setting a range in trend formulas

Roger

New Member
I am doing some simple trend forecasting. I will have to change the range of the known_x's and known-y's often. And, I'll have to do this in a number of spreadsheets forecasting different trends.


Being lazy, I don't want to manually change the range in each trend formula. I want to type "First" and "Last" in a column indicating where the range of the known_x's and known_y's in each trend formula is to start and end.


I uploaded a simple spreadsheet that better illustrates what I'm working with. It is at http://www.rogert.us/Trend.xls.


Any help will be appreciated. Thanks
 

keymaster

New Member
Roger, you can use the OFFSET formula. You need to have starting cell reference and the size of range. Both can be dynamically adjusted using formulas.


Learn more about the offset formula here: http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
 

Peter H

New Member
Roger, I have an alternate solution that does not need the offset formula - if you can accept a chart approach. I anm not sure if it requires 2007 or not. This is a trick I developed by trial and error to allow the range of chart series to be dynamically adjusted without using offset formula. ( I haven't been able to find it documented anywhere but I am sure someone will post if it has)


In your case highlight your data C3:E19

Click Home, Styles, Format as Table - Select the style required ans tick My table has headers. This gives the data table.


Create the chart - Insert, Line and select the style desired.

This gives a chart that can be formatted as required.

Select the series Y's, right click and Add Trend Line, Linear

Note that the trend line does not coincide with the calculated series "trend"


Hide the rows that are not required (manually or by filtering if it suits)

The two trendlines now line up as hidden cells are ignored.


New data can be added to the bottom of the table and the data table and chart will expand to accept it. In this case the new Y can be typed in and the row copied back onto itself or simply drag the boundary of the data table down. (This is reqired as there is some data already there)


When using this method on charts there a couple of quirks to take into account.

The heading must contain text not formula.

The right column of data must contain a number or a formula that returns a numbe when setting up the table. Once it is set up the number can be removed and the data table will function quite happily without it!


Roger, I have just looked back over your post & realised that you probably want to extrapolate your data into the futre. This can be achieved by dragging the boundary of the data chart down as far as you need hiding the data you dont need. Unfortunately you will not be able to see the predicted trend for any date where there is data but you dont want it taken into account.
 
Top