| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula €“ too many nested IFs
A1 = Original Planned Due Date
B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula €“ too many nested IFs
Based on steps 1 and 2, your 3rd step should have the logic (A1 OR B1) AND
C1, not the way you have it, which is A1 AND B1 OR C1. Dave -- Brevity is the soul of wit. "Bob" wrote: A1 = Original Planned Due Date B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula €“ too many nested IFs
Dave,
You're right! Thanks for catching my error. Can you help me modify my formula to incorporate the 3rd step and/or rewrite it so that it doesn't include so many nested IFs? Bob "Dave F" wrote: Based on steps 1 and 2, your 3rd step should have the logic (A1 OR B1) AND C1, not the way you have it, which is A1 AND B1 OR C1. Dave -- Brevity is the soul of wit. "Bob" wrote: A1 = Original Planned Due Date B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula €“ too many nested IFs
I guess I would do something like
=IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance,IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"") This seems rather complicated though. Perhaps you can split these IF-THEN-ELSE constructions into multiple logic statements, make sure that each is operating correctly, and then combine all of them into one giant formula? What I put above says, in plain English, "IF neither B1 nor C1 is blank, then calculate performance, ELSE IF neither A1 nor B1 is blank, THEN calculate performance, ELSE nothing." Dave -- Brevity is the soul of wit. "Bob" wrote: Dave, You're right! Thanks for catching my error. Can you help me modify my formula to incorporate the 3rd step and/or rewrite it so that it doesn't include so many nested IFs? Bob "Dave F" wrote: Based on steps 1 and 2, your 3rd step should have the logic (A1 OR B1) AND C1, not the way you have it, which is A1 AND B1 OR C1. Dave -- Brevity is the soul of wit. "Bob" wrote: A1 = Original Planned Due Date B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula €“ too many nested IFs
Change what I give below to this:
=IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance],IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"Missing dates") This assumes, of course, that if A1, B1, or C1 do not have dates in them, they also have nothing else in them. If that's not the case, then you have to test for the presence of data formatted as dates. Dave -- Brevity is the soul of wit. "Dave F" wrote: I guess I would do something like =IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance,IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"") This seems rather complicated though. Perhaps you can split these IF-THEN-ELSE constructions into multiple logic statements, make sure that each is operating correctly, and then combine all of them into one giant formula? What I put above says, in plain English, "IF neither B1 nor C1 is blank, then calculate performance, ELSE IF neither A1 nor B1 is blank, THEN calculate performance, ELSE nothing." Dave -- Brevity is the soul of wit. "Bob" wrote: Dave, You're right! Thanks for catching my error. Can you help me modify my formula to incorporate the 3rd step and/or rewrite it so that it doesn't include so many nested IFs? Bob "Dave F" wrote: Based on steps 1 and 2, your 3rd step should have the logic (A1 OR B1) AND C1, not the way you have it, which is A1 AND B1 OR C1. Dave -- Brevity is the soul of wit. "Bob" wrote: A1 = Original Planned Due Date B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula €“ too many nested IFs
Dave,
Since I really need to test for the existence of a value, I re-wrote my formula based on your suggestion as follows: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")),"Missing date")) The only way I know how to test specifically for the existence of a date is to use the CELL's "format" info_type. If you have a better suggestion, I am all ears. Finally, I still wish there was a way to re-write the above formula using possibly different built-in functions so that it would not be so long. If you have any ideas on how I can accomplish this, I would greatly appreciate it. Thanks again for all your help. Bob "Dave F" wrote: Change what I give below to this: =IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance],IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"Missing dates") This assumes, of course, that if A1, B1, or C1 do not have dates in them, they also have nothing else in them. If that's not the case, then you have to test for the presence of data formatted as dates. Dave -- Brevity is the soul of wit. "Dave F" wrote: I guess I would do something like =IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance,IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"") This seems rather complicated though. Perhaps you can split these IF-THEN-ELSE constructions into multiple logic statements, make sure that each is operating correctly, and then combine all of them into one giant formula? What I put above says, in plain English, "IF neither B1 nor C1 is blank, then calculate performance, ELSE IF neither A1 nor B1 is blank, THEN calculate performance, ELSE nothing." Dave -- Brevity is the soul of wit. "Bob" wrote: Dave, You're right! Thanks for catching my error. Can you help me modify my formula to incorporate the 3rd step and/or rewrite it so that it doesn't include so many nested IFs? Bob "Dave F" wrote: Based on steps 1 and 2, your 3rd step should have the logic (A1 OR B1) AND C1, not the way you have it, which is A1 AND B1 OR C1. Dave -- Brevity is the soul of wit. "Bob" wrote: A1 = Original Planned Due Date B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula €“ too many nested IFs
Bob--you're right, CELL will test for a value.
But so does NOT(OR(ISBLANK())). If the cells in question can have values other than blanks, then CELL works for what you're trying to do. If the cells in question can ONLY have dates OR blanks then either CELL or NOT(OR(ISBLANK())) will accomplish what you're looking to do. As with many things in Excel, there are many ways to accomplish the same task... Dave Dave -- Brevity is the soul of wit. "Bob" wrote: Dave, Since I really need to test for the existence of a value, I re-wrote my formula based on your suggestion as follows: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")),"Missing date")) The only way I know how to test specifically for the existence of a date is to use the CELL's "format" info_type. If you have a better suggestion, I am all ears. Finally, I still wish there was a way to re-write the above formula using possibly different built-in functions so that it would not be so long. If you have any ideas on how I can accomplish this, I would greatly appreciate it. Thanks again for all your help. Bob "Dave F" wrote: Change what I give below to this: =IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance],IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"Missing dates") This assumes, of course, that if A1, B1, or C1 do not have dates in them, they also have nothing else in them. If that's not the case, then you have to test for the presence of data formatted as dates. Dave -- Brevity is the soul of wit. "Dave F" wrote: I guess I would do something like =IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance,IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"") This seems rather complicated though. Perhaps you can split these IF-THEN-ELSE constructions into multiple logic statements, make sure that each is operating correctly, and then combine all of them into one giant formula? What I put above says, in plain English, "IF neither B1 nor C1 is blank, then calculate performance, ELSE IF neither A1 nor B1 is blank, THEN calculate performance, ELSE nothing." Dave -- Brevity is the soul of wit. "Bob" wrote: Dave, You're right! Thanks for catching my error. Can you help me modify my formula to incorporate the 3rd step and/or rewrite it so that it doesn't include so many nested IFs? Bob "Dave F" wrote: Based on steps 1 and 2, your 3rd step should have the logic (A1 OR B1) AND C1, not the way you have it, which is A1 AND B1 OR C1. Dave -- Brevity is the soul of wit. "Bob" wrote: A1 = Original Planned Due Date B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with formula €“ too many nested IFs
Dave - Thanks again!
Bob "Dave F" wrote: Bob--you're right, CELL will test for a value. But so does NOT(OR(ISBLANK())). If the cells in question can have values other than blanks, then CELL works for what you're trying to do. If the cells in question can ONLY have dates OR blanks then either CELL or NOT(OR(ISBLANK())) will accomplish what you're looking to do. As with many things in Excel, there are many ways to accomplish the same task... Dave Dave -- Brevity is the soul of wit. "Bob" wrote: Dave, Since I really need to test for the existence of a value, I re-wrote my formula based on your suggestion as follows: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")),"Missing date")) The only way I know how to test specifically for the existence of a date is to use the CELL's "format" info_type. If you have a better suggestion, I am all ears. Finally, I still wish there was a way to re-write the above formula using possibly different built-in functions so that it would not be so long. If you have any ideas on how I can accomplish this, I would greatly appreciate it. Thanks again for all your help. Bob "Dave F" wrote: Change what I give below to this: =IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance],IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"Missing dates") This assumes, of course, that if A1, B1, or C1 do not have dates in them, they also have nothing else in them. If that's not the case, then you have to test for the presence of data formatted as dates. Dave -- Brevity is the soul of wit. "Dave F" wrote: I guess I would do something like =IF(NOT(OR(ISBLANK(B1),ISBLANK(C1))),[calculate delivery performance,IF(NOT(OR(ISBLANK(A1),ISBLANK(B1))),[calculate delivery performance],"") This seems rather complicated though. Perhaps you can split these IF-THEN-ELSE constructions into multiple logic statements, make sure that each is operating correctly, and then combine all of them into one giant formula? What I put above says, in plain English, "IF neither B1 nor C1 is blank, then calculate performance, ELSE IF neither A1 nor B1 is blank, THEN calculate performance, ELSE nothing." Dave -- Brevity is the soul of wit. "Bob" wrote: Dave, You're right! Thanks for catching my error. Can you help me modify my formula to incorporate the 3rd step and/or rewrite it so that it doesn't include so many nested IFs? Bob "Dave F" wrote: Based on steps 1 and 2, your 3rd step should have the logic (A1 OR B1) AND C1, not the way you have it, which is A1 AND B1 OR C1. Dave -- Brevity is the soul of wit. "Bob" wrote: A1 = Original Planned Due Date B1 = Revised Planned Due Date C1 = Actual Due Date Im trying to write a formula that computes the following: 1) If dates exist in cells B1 AND C1, calculate delivery performance (i.e., Eary, On-time, or Late) in cell D1 ELSE 2) If dates exist in cells A1 AND C1, calculate delivery performance in cell D1 ELSE 3) If there are no dates in ((A1 AND B1) OR C1), display €śMissing date(s)€ť in cell D1 The formula I have written so far is: =IF(AND(CELL("type",B1)="v",CELL("type",C1)="v"),I F(B1C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("ty pe",C1)="v"),IF(A1C1,"Early",IF(A1=C1,"On-time","Late")))) The only part of my formula that Im having trouble figuring out is how to capture #3 above. Also, is there a way to rewrite my formula so that it doesnt use so many nested IF statements? Is there a different Excel built-in function that I can use to streamline my formula? Any help would be greatly appreciated. Thanks, Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trouble with condition nested formula | New Users to Excel | |||
| Problem with nested IF and OR formula? | Excel Discussion (Misc queries) | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Match then lookup | Excel Worksheet Functions | |||
| Excel nested IF formula question | Excel Discussion (Misc queries) | |||
Linear Mode
