Nested IF formula

-2

I have:

in *Column F*         in *Column S*
F2  Monday            S2    Orange
F3  Tuesday           S3    Mango
F4  Wednesday         S4    Grape
F5  Thursday          S5    Pawpaw
F6  Friday            S6    Onion
F7  Saturday          S7    Apple
F8  Sunday            S8    Plum

My main target in Column S is Apple regardless of what cell reference it occupies.

I want a formula that will capture the following three scenarios:

If S2 = Apple and Column F has any day, I want the formula to return Apple.
If any of the cells in Column S has Apple and the corresponding F cell is blank, I want the formula to return Apple.
If either of the cells in Column F or Column S is empty, I want the formula to return a blank cell.

*Fruits*  *Day of week*    *Results*
Apple       Monday          Apple
[Empty]     [Empty]         [Empty]
Pawpaw      Wednesday       [Empty]
Onion       Tuesday         [Empty]
Apple       [Empty]         [Empty] instead of Apple:see formula below
[Empty]     Friday          [Empty]
Plum        Tuesday         [Empty]

This formula is not working perfectly:

IF(AND(A6="",B6=""),"",IF(OR(A6="",B6="",A6="Orange",A6="Mango",A6="Plum",A6="Grape",A6="Pawpaw",A6="Onion"),"",IF(AND(A6="Apple",B6="")," ","Apple")))

Is there a formula that would achieve the desired result?

Dappy

Posted 2013-05-01T22:00:10.227

Reputation: 51

pnuts, thanks for the response. It is Excel 2003. – Dappy – 2013-05-02T00:10:15.197

pnuts: Orange, Mango, Grape, Pawpaw, Onion, Apple, and Plum are the text values in column S. The main target value is “Apple” and it could be in S2 or S70. For example, if S2 =”Mango” and F2 is whatever value, I want the formula to automatically return a blank cell. – Dappy – 2013-05-02T01:19:41.457

I have Orange in cell S2, Mango in cell S3, Grape in cell S4, Pawpaw in cell S5, Onion in cell S6, Apple in cell S7, and Plum in cell in cell S8 - I used that as an example for better understanding. S2 could be mango as the cell # and the type of fruits is not set in stone, S87 could be Plum. As long as the text value is not "Apple - main target value", the formula should return a blank cell. – Dappy – 2013-05-02T01:33:52.407

it would be much better if you could represent your excel sheet's 3 scenarios contents in tabular format... – tumchaaditya – 2013-05-02T03:42:30.000

@tumchaaditya, I have edited the original post to capture the table and what the formula is doing. Thanks for the reply. – Dappy – 2013-05-02T09:25:12.793

@Dappy - See my answer for a simpler formula: =IF(A2="Apple",A2,"")

– Kevin Fegan – 2013-05-02T17:46:19.157

Answers

=IF(A6<>"Apple","",IF(OR(B6="Monday",B6="Tuesday",B6="Wednesday",B6="Thursday",B6="Friday",B6=""),"Apple",""))

This is assuming your fruit is in A and day in B and the formula is for row 6. Correct column numbers if necessary...

tumchaaditya

Posted 2013-05-01T22:00:10.227

Reputation: 3 624

Thank you so much for the formula. It works!!! Interestingly, this modified formula also works: =IF(A2<>"Apple","",IF(OR(A2="Grape",A2="Pawpaw",A2="Onion",A2="Plum",A2="Mango",,A2=""),"","Apple")). Could you please give more insight into how the two (2) formula works. Again, thanks. – Dappy – 2013-05-02T10:15:14.410

@Dappy:it wont work if you put a weekend day, say Sunday(or text other than a weekday for that matter), in day column...to make it short, your formula does not care what is there in day column....it there is apple in fruit column, it is going to give apple as output... – tumchaaditya – 2013-05-03T03:24:20.473

@Downvoter: Care to explain the reason so that I can improve the answer... – tumchaaditya – 2013-06-10T04:17:56.910

@pnuts: for the first suggestion, I've corrected it....for the second one...if you are talking about =IF(A2="Apple",A2,""), its not fool-proof....if I enter a weekend day or any other text for that matter in the day column, this formula will not give desired outcome... – tumchaaditya – 2013-06-11T03:28:12.083

You can put an IF statement inside another IF statement, like this:

In cell S2:
=IF(CONCATENATE(S2,F2)="","",IF(CONCATENATE(S2,"|",F2)="Apple|","Apple",IF(AND((FIND(CONCATENATE("|",F2,"|"),"|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday|",1)>0),(S2="Apple")),"Apple","Other")))

This is probably not exactly what you want (but very close), you have missed the cases of:

S2="Apple" and F2 is not-empty and not-day-of-week.
S2 is empty and F2 is not-empty
S2 is not "Something-else" (regardless of what is in F2)

What do you want to return in those cases?

S2 can be [Empty], "Apple", or "Something-else". F2 can be [Empty], day-of-the-week, or "Something-else".

Altogether there are 9 possibilities, but you have indicated what you want returned in only 3 cases.

S2               F2               Return     Scenario
[Empty]          [Empty]          [Empty]    3rd Scenario
[Empty]          Weekday          ????
[Empty]          Anything Else    ????
Apple            [Empty]          Apple      2nd Scenario
Apple            Weekday          Apple      1st Scenario
Apple            Anything Else    ????
Anything Else    [Empty]          ????
Anything Else    Weekday          ????
Anything Else    Anything Else    ????

After reviewing your recent comments and modification to your question, I see that you want your formula to return this:

Fruits    Weekday    Results
Apple     Mon        Apple
[Empty]   [Empty]    [Empty]
Pawpaw    Wed        [Empty]
Onion     Tues       [Empty]
Apple     [Empty]    Apple
[Empty]   Fri        [Empty]
Plum      Tues       [Empty]

I modified your formula which you said was almost working, and I end up with this:

=IF(AND(A2="",B2=""),"",IF(OR(A2="",A2="Orange",A2="Mango",A2="Plum",A2="Grape",A2="Pawpaw",A2="Onion"),"",IF(A2="Apple","Apple","")))

After reviewing everything, I think it can be simplified to this:

=IF(A2="Apple",A2,"")

Kevin Fegan

Posted 2013-05-01T22:00:10.227

Reputation: 4 077

Kevin, thank you so much for the proposed answer. I have yet to run the formula. Back to your question for:2. S2 is not-empty and is not "Apple". If S2 is not empty and is not an Apple, I want the formula to return a blank cell since "Apple" is the main target in column S. For the first case:S2="Apple" and F2 is not-empty and not-day-of-week. F2 and other cells in column F only has day of the week data set. This is consistent for column F. However, if S2="Apple" and F2 is not empty, the formula should return "Apple." – Dappy – 2013-05-02T00:31:26.847

I'll take another look and update my answer with the new information. – Kevin Fegan – 2013-05-02T00:43:59.887

S2 Anything Else [Empty] ????

Answer: S2 Anything else could be Orange, Mango, Grape, Pawpaw, Onion or Plum in column S. If this is the case and F2 is not empty, I want the formula to return a blank cell as the main target in column S is “Apple” regardless of what cell number it is located.

Anything Else [Empty] ???? Return a blank cell Anything Else Weekday ???? Return a blank cell – Dappy – 2013-05-02T01:01:09.550

Anything Else: I only have these text values in column S Orange, Mango, Grape, Pawpaw, Onion and Plum (including the main target value, “Apple”). Therefore, Anything Else apart from “Apple” and [Empty/Weekday] should return a blank cell.

Once again, thanks for the help. – Dappy – 2013-05-02T01:01:28.003

S2 F2 Return Scenario [Empty] [Empty] [Empty] 3rd Scenario [Empty] Weekday [Empty]
[Empty] Anything Else ???? Does not apply Apple [Empty] Apple 2nd Scenario Apple Weekday Apple 1st Scenario Apple Anything Else ???? Anything Else [Empty] ???? Anything Else Weekday ???? Anything Else Anything Else ???? – Dappy – 2013-05-02T01:01:53.287

I just wanted to make sure I have this clear for S2:F2=Return. Blank:Blank=Blank, Blank:Not-Blank=Apple, Apple:Blank=Apple, Apple:Not-Blank=Apple, Anything-Else:Blank=Blank, Anything-Else:Not-Blank=Blank. Is this correct? – Kevin Fegan – 2013-05-02T01:46:45.617

S2=Apple:F2=Not-Blank = Apple Yes S2=Blank:F2=Blank =Blank Yes S2=Apple:F2=Blank = Apple Yes S2=Blank:F2=Not-Blank =Blank Yes Anything-Else:Not-Blank=Blank Yes Anything-Else:Blank=Blank Yes Apple:Not-Blank=Apple Yes Apple:Blank=Apple Yes – Dappy – 2013-05-02T02:14:48.673

=IF(AND(S2="",F2=""),"",IF(OR(S2="",F2="",S2="Orange",S2="Mango",S2="Plum",S2="Grape",S2="Pawpaw",S2="Onion"),"",IF(AND(S2="Apple",F2="")," ","Apple")))

This formula is working except that it does not capture a scenario where S2=”Apple” and F2=Blank. The formula is returning “BLANK CELL” when S2=Apple. Thanks to Trebor76. – Dappy – 2013-05-02T02:48:23.397

I have edited the original post to capture the table and what the formula is doing. – Dappy – 2013-05-02T09:25:52.907

@Dappy - I edited my answer to include an updated formula. – Kevin Fegan – 2013-05-02T17:19:21.387

thanks alot for your efforts and time. Your formula works! – Dappy – 2013-05-02T21:00:44.190

@Dappy - Great, glad I could help. Be sure to mark your favorite answer as accepted. – Kevin Fegan – 2013-05-03T01:04:16.013

1@pnuts - I don't know if I'd say "amused"... The moderator was probably considering that the question was interesting enough and the asker made some obivous efforts. Unfortunatly it was just lacking some detail which ended up being worked out in the comments. I guess I was more "amused" that on May-2-2013 21:00 the asker commented that my answer worked but then accepted an alternate answer (perhaps in error) that was more complex. – Kevin Fegan – 2013-06-08T07:36:30.680