MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Spot Trends In My Weekly Data Using Forecast Function In Numbers?

Gary,

I have a dynamic table with data for my delivery co. I have a drop down to select different weeks via indirect function. Among the things that I keep track of are total gross revenue, total orders, revenue/mile, revenue/hour, orders/hour, etc.

I’d like to add a feature to this table that shows which of these are trending up or down. Ex- last week my revenue per mile was $1.17 and this week it’s on track for $1.32, that would be up.

Can you explain how to use the forecast function?
—–
Ryan

Comments: 6 Responses to “How Do I Spot Trends In My Weekly Data Using Forecast Function In Numbers?”

    4 years ago

    I don't think the FORECAST function is what you want. That works with two lists of values: x and y. It sounds like what you want to do is to just compare last week's value with this week's value and if this week is higher then you have an "up" and if it is lower then you have a "down."

    An example of using the FORECAST function would be if you had a column with week numbers, like 1, 2, 3, 4, 5, 6 in A2:A7. Then in B2:B7 you had revenue numbers, like $1.01, $1.05, $1.08, $1.14, $1.21, $1.24.

    Then in some other cell or table, you could use this formula to predict the next value:
    FORECAST($A7+1,B2:B7,$A2:$A7)

    It would get tricky to update that for each week. The first parameter is the number of the next week. A7 holds a 6, so I have A7+1 for week 7. But you'd want that to update on its own as you add new weeks. I would use the ROWS function and make sure you always have the exact number of rows you need in that table. No extra blank rows.

    The same for the ranges. Maybe you only want the ranges to take into account the last 5 weeks instead of all weeks too. So you have to build ranges using INDIRECT instead of having them hard-coded into the function like in my example.

    It is also using linear regression to calculate this forecast. That may or may not be appropriate for what you are doing.

    Ryan
    4 years ago

    Well I should mention that the entire spreadsheet is dynamic. I’ve worked on it quite a bit and always adding features. I do not actually touch this particular table, everything updates automatically. Let me explain.

    The table is 7 rows, lots of columns. I have this formula in the top row under “date” which basically dynamically gives me the Monday of the current week:
    (Today is::$A$1+(7−WEEKDAY(Today is::$A$1,3))−7)

    I then have an “hours column” that uses this date Column As reference

    4 years ago

    Ryan: So you have one row for each day of the week, and then the columns keep growing. That isn't how I would do it at all. I would have one record per row. So each row is a date and a revenue number, etc. Think of it like a database. Then it becomes easier to perform calculations once it is one record per row.
    It will be very hard to perform calculations based on values that extend beyond the one week in a column the way you have it.

    Rusn
    4 years ago

    Here are some pics, will have to scroll
    https://imgur.com/a/xqgGjyd

    Rysn
    4 years ago

    Yeah i have it set up the way you recommended I think (I’ve watched all your numbers videos dozens of times). It’s really super complex. I’m scared it’s going to break numbers.

    Ryan
    4 years ago

    Anyway what i wanted to do was add another section that broke down the deliveries per platform. So I could see how I am Doing per platform. And to see if I was trending up or down. Preferably not just using the previous weeks data, but like 2 months worth of data, with the newer data getting more weight or importance.

Comments Closed.