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
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.
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
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.
Here are some pics, will have to scroll
https://imgur.com/a/xqgGjyd
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.
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.