Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

  1. If S2 = Apple and Column F has any day, I want the formula to return Apple.
  2. If any of the cells in Column S has Apple and the corresponding F cell is blank, I want the formula to return Apple.
  3. 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?

share|improve this question
    
pnuts, thanks for the response. It is Excel 2003. –  Dappy May 2 '13 at 0:10
    
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 May 2 '13 at 1:19
    
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 May 2 '13 at 1:33
    
it would be much better if you could represent your excel sheet's 3 scenarios contents in tabular format... –  tumchaaditya May 2 '13 at 3:42
    
@tumchaaditya, I have edited the original post to capture the table and what the formula is doing. Thanks for the reply. –  Dappy May 2 '13 at 9:25
show 1 more comment

2 Answers 2

up vote 0 down vote accepted
=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...

share|improve this answer
    
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 May 2 '13 at 10:15
    
@Dappy If it works then mark the answer as accepted. –  Lee Taylor May 2 '13 at 12:20
    
@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 May 3 '13 at 3:24
    
@Downvoter: Care to explain the reason so that I can improve the answer... –  tumchaaditya Jun 10 '13 at 4:17
    
The -1 was mine, because I think acceptance may mislead. The source data is, for example, Tuesday not Tues and B6 =”Tues” is not the same as B6 contains “Tues”. Also, your formula is 90 characters where =IF(A2="Apple",A2,"") (already provided in another answer to this question) is only 21 and much easier to understand (I think). –  pnuts Jun 10 '13 at 15:14
show 3 more comments

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:

  1. S2="Apple" and F2 is not-empty and not-day-of-week.
  2. S2 is empty and F2 is not-empty
  3. 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,"")
share|improve this answer
    
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 May 2 '13 at 0:31
    
I'll take another look and update my answer with the new information. –  Kevin Fegan May 2 '13 at 0:43
    
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 May 2 '13 at 1:01
1  
@Kevin Would it amuse you to know that a moderator "found no evidence to support it" where 'it' was the suggestion this was a very low quality question? (Reams of comments and the answer is =IF(A2="Apple",A2,"") - and not even accepted in favour of =IF(A6<>"Apple","",IF(OR(B6="Mon",B6="Tues",B6="Wed",B6="Thu",B6="Fri",B6=""),"A‌​pple","")) {which does not even conform to the stated cell contents}. –  pnuts Jun 7 '13 at 16:54
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 Jun 8 '13 at 7:36
show 9 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.