Find forecast date using actual data

Hi,

I have a table of planned dates with corresponding planned % values.
I am trying to get a formula which will help calculate a forecast completion date, given the status at a particular date.
Explained well in attached sheet.


Thanks in advance

Hi

Use for delays:

Formula:

=INDEX($C$3:$C$15,MATCH($F$2,$A$3:$A$15,1))-$F$3%*INDEX($C$3:$C$15,MATCH($F$2,$A$3:$A$15,1))/INDEX($B$3:$B$15,MATCH($F$2,$A$3:$A$15,1))

Hi Jose Augusto,

Its giving value 52.68 whereas delay is actually 21 days

Hi
Sorry for the mistake

Formula:

=INDEX($C$3:$C$15,MATCH($F$2,$A$3:$A$15,1))-$F$3*INDEX($C$3:$C$15,MATCH($F$2,$A$3:$A$15,1))/INDEX($B$3:$B$15,MATCH($F$2,$A$3:$A$15,1))

(No $F$3% but $F$3)

Hi Jose Augusto,

When I change the date, its giving error

Hi

What date are you using?

Hi

The formula I provided earlier does not work correctly. Try this

Formula:

=SUM($C$3:INDEX($C$3:$C$15,MATCH($F$2,$A$3:$A$15,1)))

-$F$3*SUM($C$3:INDEX($C$3:$C$15,MATCH($F$2,$A$3:$A$15,1)))/

INDEX($B$3:$B$15,MATCH($F$2,$A$3:$A$15,1))

See the file