Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

enter image description here

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?

share|improve this question
    
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
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.