Excel - forecast future date based on average number of days between a variable number of dates in the past

I have anywhere from two to six dates for events in the past and based on the average spread from each one to the other, I need to predict when the next event will occur.

From the screenshot, I essentially want to take the average of (C4-D4),(D4-E4),(E4-F4) and skip (F4-G4) since it is blank. Then I want to add the average number of days to the most recent value (C4) to derive (A4), the predicted next occurrence.

I want to have one formula in B4 that comes up with the average days and skips the calculation if one or both of the cells is blank.

I tried Max-Min/CountIf:

=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")

But it comes up with too low of a number each time, in the case of row 5, 159 when it should be 214, and row 6 should be 337. When I tried to use AVERAGE across the dates, I did not get days, I got the average date.

Answer

Your formula should subtract 1 from the denominator, because it is the differences you want to count not the actual numbers.

=IFERROR((MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")

If you want to skip the helper column:

=IFERROR(MAX(C4:G4) + (MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")

---

You can also use the FORCAST:

=FORECAST(0,C4:G4,ROW($1:$5))

Or even INTERCEPT:

=INTERCEPT(C4:G4,ROW($1:$5))

These two use the trend and not the average so they will come up with a different value if the differences vary greatly.