More Planned and Actual Cycle Times
 

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.

More Planned and Actual Cycle Times
 

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

regards
FSt1

"PAL" wrote:

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.

More Planned and Actual Cycle Times
 

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" wrote:

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

regards
FSt1

"PAL" wrote:

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.

More Planned and Actual Cycle Times
 

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" wrote:

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" wrote:

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

regards
FSt1

"PAL" wrote:

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.

More Planned and Actual Cycle Times
 

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" wrote:

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" wrote:

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" wrote:

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

regards
FSt1

"PAL" wrote:

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.

More Planned and Actual Cycle Times
 

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

"PAL" wrote:

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" wrote:

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" wrote:

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" wrote:

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

regards
FSt1

"PAL" wrote:

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.