| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 cycle logarithmic? | Charts and Charting in Excel | |||
| How do I keep result from 1 iteration cycle to use in next cycle? | Excel Discussion (Misc queries) | |||
| how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel | |||
| how do I do a Planned vs Actual start date & end date graph | Charts and Charting in Excel | |||
| Planned Open Market Days | Excel Discussion (Misc queries) | |||
Linear Mode
