# Excel trend line intercept

I have an Excel graph with a linear trend line to keep track of users who are updated with a newer version of software:

I have 660 users, and the trend line predicts where the number updated reaches 660 to indicate updates complete. Is there a way for it to either give me an actual value for that intercept, or, more conveniently, draw a vertical intercept line where the trend line is projected to hit that number?

-
Are the two axes of your graph Number Updated and Date? And if so, are you looking for a prediction as to what date all the users will have received the update? Please update your question to clarify. – music2myear Feb 17 '12 at 14:44

If you're looking for a predicted DATE when the Number Updated reaches 660, you need to write some formulas.

I did this in a spreadsheet I was using to track Backup growth at a previous job. The solution does not involve the graph, it uses the FORECAST() function of Excel.

The syntax is:

``````=FORECAST(X, Yrange, XRange)
``````

In your case X equals 660, Yrange is the Date, and Xrange is the Number of Successful Updates.

So if your dates are in column A and the TOTAL number of successful updates is in column B, and you've got less than a thousand values entered already, you'll put the following formula in a spare cell somewhere:

``````=FORECAST(660, A1:A1000, B1:B1000)
``````

In my experience I was unable to tell it to just use all column values (A:A) without it failing somehow, so I had to set some arbitrary limit (A1:A1000) for it to work.

Format the cell you put this formula in as Date and you'll get a prediction as to when your number of installs will equal the number of users.

Excel Tips was a great help in finding this information for me back when I was building the spreadsheet myself: http://excel.tips.net/T002573_Using_the_FORECAST_Function.html

-
Worked like a charm. Thanks muchly. – Brian M. Feb 17 '12 at 17:28
You're welcome. – music2myear Feb 17 '12 at 17:35
@music2myear I've just tried your method but it's a little off for my data. I suspect it's because my data has a set y-intercept of 0. How do I account for this with the formula? I tried adding (0,0) as data but that didn't affect the value. – Rory Feb 17 '12 at 18:23
Rory, please post a new question with your specific needs and we'll be happy to check it out and find an answer. – music2myear Feb 17 '12 at 18:26
If you like, you can then post a link to the question here so I'll see that you've posted it. – music2myear Feb 17 '12 at 18:27