Forecast + Offset Formula Needed

Hello,
Can anyone help me with making some changes to a FORECAST formula?

I need a Forecast formula that includes the values of price every four hours over the last five instances and the current price. Below are formulas I've been working with.

I'm thinking adding an Offset might be the best way to go because it'll allow me to skip between the prices and only factor in the prices I want.

That's the gist of what I'm trying to do, be able to include any combination of numbers in a Forecast formula without the numbers having to be in continuous order.

Basic Forecast Formula:

Please Login or Register  to view this content.

An Offset Version:

Please Login or Register  to view this content.

Please see attached spreadsheet for a better understanding. Thanking you for taking the time to read this post and for any and all help you can provide.

I confess I'm struggling to follow the required logic... and the pseudo forecast formula is slightly odd given the offsets applied are on width rather than height.

Could you post a revised sample with a number of expected results ? (calculated manually of course)

The more expected results in the sample the easier it is to "cross the t's and dot the i's" from a logic perspective.

Can you outline how you arrive at 1.3616 for #18 ?

If the Forecast is to include #5, #9, #13, #17 & #18 I would expect 1.36181

Please Login or Register  to view this content.

First, I assumed that in your "real" version the hours 13:00 etc do not exist - only every 4th hour is listed.

The differences stem from your use of "known x's".

Using the example of forecast 2 (#18) you are using:

Please Login or Register  to view this content.

whereas my suggestion (condensed - removed Boolean Falses) evaluates to:

Please Login or Register  to view this content.

ie the same values for y but the known x's are different

This difference in approach will cause a variance where the step between known x's is inconsistent as will be the case for #18-20, #22-24 etc...

If you want to use 5, 1 to 5 for x & known x's then let us know but that approach to me would appear to be inconsistent (ie consistent step assumed despite reality)

To reiterate my suggestion assumes that only 1,5,9,13,17,21 etc have time values in Col B.

Originally Posted by artiststevens

Your suggestion is correct in assuming that 1,5,9,13,17,21 etc are the only ones that have time values.

In which case remove the other values as these directly affect the results of the formula I gave you.

The results will be identical at that point.

My understanding is/was that there would be no "hour" values in the non-yellow cells in Column B yet in your samples they continue to persist.

You must remove them as they directly affect the formula I gave you - once removed the results of the Array match your "manual" calculations.

It all depends on where you would put the first formula.

In the example given we have assumed first formula is located at point of first valid result (ie where you have at least 17 transactions)

If we apply the same logic for 6 instances then first formula would be located in row 23 and you would adapt the precedent range to be B3:B23 etc as opposed to B7:B23 as would be the case for 5 instances.

I was able to change the the number of instances with ease. Thank you again DoneyOte! I tried to rep you again but they site won't let me, but know I really appreciate it.