IF-formula with several true values?

**Tinwelende** · 06-16-2013, 03:38 PM

I have a cell where the data can change between a number of different letter combinations. I know what combinations the cell might contain. SOME of these combinations should not be shown, if that particular combination would occur.

So I need an IF-formula for creating a new cell that says something like:

"IF the cell I'm thinking of contains AA or Aa or Bb or sgsg or dsds or Bb or BB, this cell should say "". If it contains anything else than the previous stated combinations, it should just copy the previous cell as it is."

I can fill in the AA's and Aa's and everything in the formula by myself (there are more combinations than the ones I wrote here), but I don't know how I add more true values to the formula than one.

**etaf** · 06-16-2013, 04:33 PM

do they need to be case sensitive

=IF( OR( Cell="AA", cell="BB", cell="sgsg", etc ), true , false)

if you need case sensitve use EXACT

=IF( OR( EXACT(Cell,"AA"), EXACT(cell,"BB"), EXACT(cell,"sgsg"), etc ), true , false)

to get

this cell should say "". If it contains anything else than the previous stated combinations, it should just copy the previous cell as it is."

the true / false

replace with

"", cell)

**Tinwelende** · 06-16-2013, 05:06 PM

I do need them case sensitive, but when I tried to enter the formula, I got an error. Did I do something wrong? This is what I tried to write:

=IF( OR( EXACT(AG4,"AA"), EXACT(AG4,"BB"), EXACT(AG4,"sgsg") ), "" , AG4)

**martindwilson** · 06-16-2013, 05:17 PM

Formula:

Please Login or Register  to view this content.

works fine for me
maybe you need; instead of ,
=IF(OR(EXACT(AG4;"AA");EXACT(AG4;"BB"),EXACT(AG4;"sgsg") );"";AG4)

**Haseeb Avarakkan** · 06-16-2013, 05:19 PM

Enter all the combinations in a range, ie A2:A20, then;

=IF(SUMPRODUCT(EXACT(AG4,A2:A20)+0),"",AG4)

**Tony Valko** · 06-16-2013, 08:48 PM

Here's another one...

List all of the combinations in a range of cells, say A2:A5.

Then, this array formula**:

=IF(COUNT(FIND(A2:A5,AG4)),"",AG4)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**Tinwelende** · 06-17-2013, 01:15 AM

Thank you, Tony! That sounds like a perfect way of doing it! Though, nothing happens when I hit Ctrl, Shift, Enter. Am I doing something wrong? I just choose a cell, then hit Ctrl, Shift, Enter. I kind of expected something to pop up, but nothing did.

**Tony Valko** · 06-17-2013, 09:29 AM

Did you actually enter the formula in a cell?

Select the cell where you want the formula.

Type the formula but don't hit the enter key like you normally would. Use the key combination of CTRL, SHIFT, ENTER.

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.

Or, maybe the formula is returning a blank? My understanding is that when cell AG4 contains one of the matching entries the formula should return a blank.

**Tinwelende** · 06-17-2013, 09:51 AM

Ooooh, I've misunderstood the whole thing! I hit the combination BEFORE I entered the formula into the cell. I didn't understand that you had to do the combination afterwards!

Though, it still doesn't work. It gives me an error sign every time I try to replace the end of the formula: A2:A5,AG4)),"",AG4) with the correct cells.

Here's a workbook with an example of my data:

Workbook genes expressed.xlsx

**Tony Valko** · 06-17-2013, 10:00 AM

Ok, it looks like the letter combinations are in the range A2:A28.

So what cell(s) do you want to compare those combinations to? Where do you want the formula results to appear? It would be better if you could show us what results you expect. If you've already done that in the sample file then I am completely lost!

**Tinwelende** · 06-17-2013, 10:07 AM

I've given one example in the workbook of how I want the result to be:
__________
Only the genes that will be expressed:
SgsgUu
__________

The code SgsgUu is what should come out after the formula has done it's job.

Basically, I need it to merge J2 through P2, but only add the cells that are not listed in the "Genes that will not be expressed"-column.

So if one cell under "Gene code result" contains, for example, Pp, it should not be merged. If it contains anything other than what's in the "Genes that will not be expressed"-column, it SHOULD be merged.

**Tony Valko** · 06-17-2013, 10:28 AM

OK, I think I understand.

Hmmm...

Well, none of the formulas we suggested so far will do what you want.

You can either use a very long formula made up of a bunch of concatenated IF functions...

Array entered**:

=IF(OR(EXACT(A$2:A$28,J2)),"",J2)&IF(OR(EXACT(A$2:A$28,K2)),"",K2)&IF(OR(EXACT(A$2:A$28,L2)),"",L2)&IF(OR(EXACT(A$2:A$28,M2)),"",M2)&IF(OR(EXACT(A$2:A$28,N2)),"",N2)&IF(OR(EXACT(A$2:A$28,O2)),"",O2)&IF(OR(EXACT(A$2:A$28,P2)),"",P2)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Or, you could use a VBA concatenation function like the one here:

http://www.excelforum.com/tips-and-t...geravatar.html

If you want to use the VBA function let me know.

**Tinwelende** · 06-17-2013, 10:44 AM

I can't use macros, since other people need to be able to access this document and just fill in a couple of cells and then the result should appear instantly.

But when I try to enter the array formula (into R2) and hit Ctrl, Shift and Enter - nothing happens. No brackets appear around the formula or anything.

**Tony Valko** · 06-17-2013, 10:51 AM

Here's the file with the formula entered.

Workbook genes expressed(1).xlsx

**Tinwelende** · 06-17-2013, 11:24 AM

Thank you! It seems to be exactly what I wanted!

Thank you soooo much! :D

**Tony Valko** · 06-17-2013, 11:29 AM

You're welcome. Thanks for the feedback!

IF-formula with several true values?

LinkBack

Thread Tools

Rate This Thread

Display

IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Re: IF-formula with several true values?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions