Calculating percentage complete based on actual and not forecasted

[greeneggsandsam]

Any help would be greatly appreciated on this one.
I have an idea on how it should go just not sure how to put it together and its driving me nuts

[FDibbins]

Not sure what your expected answers would be, but give this ARRAY formula a try in A2, copied down...
=CHOOSE(MATCH(MAX(IF(B2:E2<=$G$1,B2:E2),0),B2:E2,0),0.25,0.5,0.75,1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[greeneggsandsam]

It works with one exception...
If the dates occur in the past I want it to calculate the total percentage to date. If the date has been passed then it should ignore that date.
For example if B2 & D2 occured in the past then the value in A2 should be 50%. Then once the date in C2 has changed to an actual completion date then the value in A2 would be 85%. And so on until B2:D2 were all actual completion dates.

[FDibbins]

Oh ok, so they are cumulative?

[FDibbins]

Try this...
=IF(B2<=$G$1,0.25,0)+IF(C2<=$G$1,0.35,0)+IF(D2<=$G$1,0.25,0)+IF(E2<=$G$1,0.15,0)

= 25% (only D2 is past)

[greeneggsandsam]

PERFECT!!! Didn't realize IF statements could be summed. Face Palming my self right now.
Thanks again

[FDibbins]

Glad we got it resolved for you, and thanks for the feedback