I have a set of predictions for when a number of different events will occur. Let's say they're predictions of how long a a bunch of car rides will take, eg. each car ride has a number of predictions by a few people, updated periodically as the car ride goes along to try to converge on a more accurate prediction.
I would like to use the data from a) these past predictions, and b) a few starter estimates to predict how long my next car ride is likely to take. I'd like the prediction to have an intuitive expression of confidence as well, eg. "Based on your starter estimate of 10 hours and subsequent estimate of 10.5 hours, I'm 90% confident that the ride will take between 9 and 12 hours" or something similar.
What are some ways I can use to make this prediction? I was thinking an OLS regression, but I wasn't sure how to make that work since all of the rides are of wildly varying durations.
For convenience I would like to do this in a spreadsheet, but that's not a firm requirement.
There is quite a bit of literature on "combining forecasts". See this example.
A simple, spreadsheet friendly approach would be as follows:
Assume we have the same $n$ people making predictions for every ride (otherwise, past performance measures nothing). You want to select non-negative weights $w$ such that $\sum_1^n w_i = 1$ so as to minimize some measure of error (e.g., absolute difference between predicted and actual arrival time).
Unfortunately, as you pointed out, rides can vary in length. If we simply minimize the average absolute error, then we may bias our weights to be better for longer rides at the expense of being (relatively) far off for short rides.
One approach is to simply normalize the error by the actual trip time, so that your errors are absolute percentage errors of the total ride time (you'd expect larger errors for longer rides).
Provided you normalize your error, you now need to normalize when your predictions were made by expressing the time of forecast in terms of the total fraction of the distance traveled (since we don't know the fraction of the total time traveled). This will re-scale your problem to have an $x$ axis run from $0$(0 miles traveled) to $1$ (all miles covered) and a y axis be in percentage error.
You'll likely need to bin your x-values so you have more than one prediction at various fractions of the total trip mileage. Then, for each bin, use Excel's solver to find the optimal weights to minimize the average percent error across all rides (where weights are the same for a given person across trips).
Anyway, one way among a large number of possibilities.