Help with formula – too many nested IFs

B

Bob

A1 = Original Planned Due Date
B1 = Revised Planned Due Date
C1 = Actual Due Date

I’m 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"),IF(B1>C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("type",C1)="v"),IF(A1>C1,"Early",IF(A1=C1,"On-time","Late"))))

The only part of my formula that I’m having trouble figuring out is how to
capture #3 above.

Also, is there a way to rewrite my formula so that it doesn’t 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
 
D

Dave F

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
 
B

Bob

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
 
D

Dave F

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
 
D

Dave F

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Bob

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"),IF(B1>C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("type",C1)="v"),IF(A1>C1,"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 said:
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 said:
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 said:
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


:

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.


:

A1 = Original Planned Due Date
B1 = Revised Planned Due Date
C1 = Actual Due Date

I’m 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"),IF(B1>C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("type",C1)="v"),IF(A1>C1,"Early",IF(A1=C1,"On-time","Late"))))

The only part of my formula that I’m having trouble figuring out is how to
capture #3 above.

Also, is there a way to rewrite my formula so that it doesn’t 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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave F

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 said:
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"),IF(B1>C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("type",C1)="v"),IF(A1>C1,"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 said:
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 said:
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.


:

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


:

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.


:

A1 = Original Planned Due Date
B1 = Revised Planned Due Date
C1 = Actual Due Date

I’m 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"),IF(B1>C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("type",C1)="v"),IF(A1>C1,"Early",IF(A1=C1,"On-time","Late"))))

The only part of my formula that I’m having trouble figuring out is how to
capture #3 above.

Also, is there a way to rewrite my formula so that it doesn’t 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
Click to expand...
Click to expand...
Click to expand...
 
B

Bob

Dave - Thanks again!
Bob


Dave F said:
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 said:
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"),IF(B1>C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("type",C1)="v"),IF(A1>C1,"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 said:
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.


:

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.


:

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


:

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.


:

A1 = Original Planned Due Date
B1 = Revised Planned Due Date
C1 = Actual Due Date

I’m 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"),IF(B1>C1,"Early",IF(B1=C1,"On-time","Late")),IF(AND(CELL("type",A1)="v",CELL("type",C1)="v"),IF(A1>C1,"Early",IF(A1=C1,"On-time","Late"))))

The only part of my formula that I’m having trouble figuring out is how to
capture #3 above.

Also, is there a way to rewrite my formula so that it doesn’t 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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Different Functions in One Cell Cancel Each Other Out - How can I fix this? 1
Nested IFs and multiples 7
Need Help 4
Calculate elapsed time excluding specific timeframe 10
Time Calculations Help 5
Need help with formula 2
Need help with formula 2
formula to add year to date figures 3

Top