More Planned and Actual Cycle Times

[PAL]

Here is what I have:

A2 Milestone 1 Planned
B2 Milestone 1 Actual
C2 Milestone 2 Planned
D2 Milestone 2 Actual
E2 Year (this is the year for the date in D2 or C2)

Using a nested conditionals, I would like the output to be:

Where D2 (or C2 if planned for the future) is in a Year X (from E2), then...

D2 - B2; if there is no data in D2 for calculation, then
C2 - B2; if there is no data in B2, then
C2 - A2; if no data in A2, then blank (don't want to see #value!).

Should these calculations been done separately then put into one column.....

Thanks.

 

[FSt1]

hi,
what is the significance of D2 or C2 being in year x and if it isn't, then
what?

regards
FSt1

 

[PAL]

People input a date in one column as planned or actual. When I list the
output I need to do it by year. So, I hope to use it as a filter to show
that for all of Milestone 2 who end in 2008, the cycle time is .....Hope that
helps.

 

[FSt1]

hi
the year in E2 should have no significance in the formula and only used when
you filter for report purposes.. try this...
=IF(D2<>"",D2-B2,IF(B2<>"",C2-B2,IF(A2<>"",C2-A2,"")))

regards
FSt1

 

[PAL]

This works well. Thanks.

Any ideas for missing data (we always have some degree of non-compliance),
where we would get the pesky "#VALUE!" error.

Would the IFERROR function come in here. I tried it and it blew up.
Perhaps there is to much going on with the formula.

 

[FSt1]

hi
the value error should not occur here. i tested all 4 cell with and without
data. you either get a number of you get nothing.
as to "some degree of Non-compliance" concerning missing data.....
you'll have to get your users in a head lock about that.

Regards
FSt1