43 responses

  1. Cyril Z.
    January 24, 2011

    Hello Hui,
    Nice post indeed.

    We use trend analysis here with burndown charts (dev teams) to estimate when we’ll reach milestones.

    We use line of best fit for the 4 last points of measurement (once in a week) plus forecasted effort for next week. This gives us a good view with the actual effort and the trend.

    Regards

    Reply

  2. Walter
    January 24, 2011

    Excellent post!!! I’m needing something like this in my work. Can I use this method that will help forecast if there are multiple variables: temperature, call volume, billing and marketing campaigns by week?

    Reply

  3. Istiyak
    January 24, 2011

    Wel come Hui, nice post. It will help me in future time limit settings for task given to the team….

    Reply

  4. Fred
    January 24, 2011

    hi Hui,

    I have a question. I understand the equal triangle concept. However, isn’t it still an “eye ball” approach to find out the best fit line? If we have two individuals using the same approach, they will come up with different equation to the manual trend line, right?

    Fred

    Reply

  5. dcwright
    January 24, 2011

    Great ! Watch out Chandoo……… Hui moving up?

    I use trending in Fleet Gas Mileage analysis. Sometimes the odometer mileage,costdata, date are missing for a Fleet Car, and I have to estimate the mileage, based on the last two entries delta, Delta = MPG2-MPG1. Works Okay, but would like to have an EXCEL Formula to predict the missing mileage value automatically. Of course, the Fuel Cost would have been nice to add to the database. Not bad for Governement work……….

    Reply

  6. Ramya
    January 24, 2011

    This is really exciting and a very insightful post . Thanks a lot ….. would love to see more posts on forecasting 🙂

    Reply

  7. Zarif
    January 24, 2011

    This is childs play, anyone who has the most basic knowledge in algebra can do this and every highschool kid is able to do this. No offense intented, but the content of this post is quite disappointing.

    Reply

  8. Hui…
    January 25, 2011

    @ Cyril, Walter, Istiyak, DCWright, Ramya, Thanks for your appreciation
    @ Fred, You are correct and we will be looking at more scientific/rigorous approaches in part 2 of this series.
    @ Zarif, I never said it wasn’t childs play and I know lots of people who have never studied algebra or finished even mid level high school who use Excel daily, and this post was aimed at them. I also didn’t think it is appropriate to jump into multiple variable regressions or multi dimensional polynomials on day 1. I think it is more important for people unfamiliar with a technique to understand the how and why rather than just how to do something. As I said at the start this is a 3 part series.

    Reply

  9. chrisham
    January 25, 2011

    Hui, great post….. Can’t wait to see the next 2 installments….. And Yes, this what makes a great post, you gradually walk through the content knowing very well that you have a wide range audience, of which some of them would greatly benefit from this “Childs Play”…..

    Reply

  10. Ange
    January 25, 2011

    very good post. It reminded me of things that long ago that I did not use

    Reply

  11. Vijay Sharma
    January 25, 2011

    WTG Hui… looking forward to the next installements..

    ~Vijay

    Reply

  12. Fred
    January 25, 2011

    I’m a slow learner. It’s always good to refresh some of the basic skills that I have almost forgotten over the years. Thank you very much, Hui.

    Reply

  13. bill
    January 25, 2011

    Wonderful post, great approach, getting the basics right is much more important than any other step… thank you, thank you. please continue the lessons. -bill

    Reply

  14. Mark
    January 26, 2011

    Hui,
    Awesome post. I am looking forward to this week’s topic.

    Reply

  15. Manny
    January 27, 2011

    Nice post…I Have a question on where the 28 came from, I thought it should read 30/25.
    Y1/X1 = Y2/X2

    Y1 = 38 – 8 = 30

    X1 = 30 – 5 = 25

    Y1/X1 = 30/28 = 1.2

    Reply

    • vishwanath
      March 20, 2013

      Ur the best to analyse,, pls ask the solution over the numbers……

      Reply

  16. Hui…
    January 27, 2011

    @Manny
    Good pickup, I have updated the post

    Reply

  17. Vinicius
    March 10, 2011

    I’ve seen some charts in which there’s some sort of “ball” like in this last chart, but this one is different because it’s dynamic and “moves” according to the data, I’ve searched everywhere but couldn’t find this, can anyone help me out?

    Reply

  18. Hui…
    March 10, 2011

    @Vinicius
    All the charts and data from the 3 Are You trendy ? posts are in the file at
    http://chandoo.org/wp/wp-content/uploads/2011/01/Are-You-Trendy-Complete-Examples.xlsm
    On the Trendlines, Trendy, Part 1 and Part 2 tabs there are examples of dynamic charts, including the one above.

    or you can look here at:
    http://chandoo.org/wp/2010/11/11/highlight-data-points-scatter-line-charts/

    Reply

  19. Jack
    November 30, 2011

    Excellent post!!!

    Reply

  20. Gabriole VanKeith
    June 22, 2012

    In your Equal Triangles section do you also need make a fix?  Change 24.07 to 26?

    We can rewrite this as
    ? = 8 + 1.2 x 15 = 24.07

    Reply

    • Hui…
      June 23, 2012

      @Gabriole
      Thanx and fixed
      I think need to sternly discipline my proof reader

      Reply

  21. R
    July 16, 2012

    This can be useful, but it is assuming a linear relationship. 

    OF course, some data sets will have a linear relationship, but mostly there will be cyclical and periodic changes that a linear relationship will not be able to take account of. Essentially the best would be exponential weighted averages. 

     

    Reply

  22. Yitno Isrofi
    October 22, 2012

    Hi Guru,

    Like to ask bout forecast as follow:

    YEAR
    SALES

    2000
       300.965

    2001
       299.597

    2002
       317.748

    2003
       354.629

    2004
       483.148

    2005
       533.917

    2006
       318.904

    2007
       433.341

    2008
       603.774

    2009
       486.088

    2010
       764.710

    2011
       894.164

    2012
    ?

    2013
    ?

    2014
    ?

    2015
    ?

    2016
    ?

     
    What should I do? please help, thanks in advance.. 

    Reply

  23. vishwanath
    March 20, 2013

    Be specific over calculation part…….

    Reply

  24. John
    November 7, 2013

    working on seasonal rainfall trends. I produce a trend and line of best fit. however I want to know the meaning of the equation y=ax + b. what is a, x and b. where do I get the numbers to fit in the equation

    Reply

    • Hui…
      November 7, 2013

      @John
      The equation for any straight line can be expressed as y=ax+b
      where a is the gradient of the line and
      b is the Y intercept of the line, ie the y value when x=0

      In Excel you can use the Trend, Intercept or Linest functions
      ex: for the Equation y = 2.5x + 4.5
      x y
      1 7
      3 12
      7 22
      15 42

      Slope 2.5 =SLOPE(C3:C6,B3:B6)
      Intercept 4.5 =INTERCEPT(C3:C6,B3:B6)

      Linest 2.5 4.5
      =LINEST(C3:C6,B3:B6)

      Reply

  25. Ivan Santos
    March 18, 2014

    i know im late but would this be something yall can help me out with, the file i uploaded conatains the data that i mirrored from an article, if you can advised me on how to obtain the points in the file.

    heres the link to a thread i started:

    http://forum.chandoo.org/threads/how-to-do-call-volume-forecasting-for-service-desks.15479/

    Reply

  26. setia
    January 11, 2015

    hello,
    how to get realtime spot:data forex from http://www.binary.com to make dynamic Sparklines chart tick by tick

    thank you

    Reply

  27. Substantial Rushing 3 Serious Auto racing 3 Get into Substantial Speeding 3 ‘cheat’ Realistic Speeding 3 Tips Serious Pounding 3 Hackers.
    August 4, 2016

    It’s a shame you don’t have a donate button! I’d without a doubt donate to this excellent blog!
    I guess for now i’ll settle for book-marking and adding your RSS feed to my Google account.
    I look forward to new updates and will talk about this blog
    with my Facebook group. Talk soon!

    Reply

  28. Construction Management Software
    March 2, 2017

    Oh my goodness! Incredible article dude! Many thanks, However I am experiencing
    troubles with your RSS. I don’t understand why I can’t
    join it. Is there anybody getting identical RSS issues?
    Anyone that knows the solution can you kindly respond? Thanx!!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top
mobile desktop