0

I want to tell certain combinations of genes that make bacteria species.

So:

sample number, gene A, gene B, gene C.

The formula should be:

  • If all 3 are negative then it is a rest group
  • If A and B are positive it is bacterial strain Pn
  • If A and C are positive it is bacterial strain Ps

How can I achieve that?

Improve this question
1
  • 2
    How is your table layout like? And what happens if A, B and C are positive? Or is that not possible?
    – Jerry
    Oct 1, 2013 at 11:46

3 Answers 3

Reset to default
2

This forumula does it, although I've made some assumptions based upon your post!

  =IF(AND(B1<0,C1<0,D1<0),"Rest",IF(AND(B1>=0,C1>=0,D1<0),"Pn",IF(AND(B1>=0,C1<0,D1>=0),"Ps","")))

enter image description here

Stick the forumula into the first row, then drag down the full length of the table.

Improve this answer
1
  • 1
    Good, simple answer. Also, sorry about the mix up on my suggested edit. A moderator edited the post before the edit was approved, which removed a lot of the text and made my edit look different than intended.
    – John Bensin
    Oct 1, 2013 at 14:27
1

For a slightly different approach convert your data into an Excel Table (this allows for easier data maintenance and analysis). Then, assuming the following table:

bacteria species

Use the following function for your bac_sp column:

=IF(AND(SIGN([gene a])=-1,SIGN([gene b])=-1,SIGN([gene c])=-1),"Rest",IF(AND(SIGN([gene a])=1,SIGN([gene b])=1,SIGN([gene c])=-1),"Pn",IF(AND(SIGN([gene a])=1,SIGN([gene b])=-1,SIGN([gene c])=1),"Ps","")))

Using the table allows better name usage (e.g. [gene a] rather than B2) and usage of the sign function prevents the need for explicit greater/less than comparisons. You can also create Pivot Tables based upon this and get some basic stats like count of each type of strain.

EDIT: To avoid a misunderstanding regarding the above function, please review the Excel function Sign. This function simply evaluates whether a particluar value is positive, negative or zero and returns 1,-1, or 0 respectively. So, it doesnt' matter what the coded value is, simply whether it's positive or negative. Here's a sample with more diverse sample data (and the same bac_sp formula):

additional example

As you can see, the results are the same, regardless of the coded values in the gene columns. The function's arguments of -1, 1 are based upon the possible return values of the Sign function, not based upon the values in the data table.

Improve this answer
3
  • 1
    This isn't good - what happens if the value was -2? By hard coding actual values into the Excel sheet like this, you've made it impossible to update.
    – Dave
    Oct 1, 2013 at 12:58
  • This doesn't look for the coded value, rather it looks at the value's sign (see Excel Sign function), which returns -1 for negative, 1 for positive (and 0 for 0). So, it evaluates solely whether a number is negative or positive, regardless of its value.
    – dav
    Oct 1, 2013 at 14:45
  • I had no idea. This is great. +1
    – Dave
    Oct 1, 2013 at 19:44
1

I would use this if you have the genes are in column A, B and C respectively and starting on row 1:

=CHOOSE(SUM((A1<=0)*1,(B1<=0)*2,(C1<=0)*4,1),"","","Ps","Pn","","","","Rest")

It basically checks for the sign of each cell's and multiplies them by a specific number.

If all are negative, SUM returns 8 so that Rest is picked.

If A and B are positive, we get a sum of 5, hence Pn.

If A and C are positive, we get a sum of 3, hence Ps.

Improve this answer
2
  • 1
    FWIW, it looks like this gives a false Pn result if A and B are negative and C is positive. Also, you're correct, it needs an IFError wrapper for all positive, otherwise it returns #Value!.
    – dav
    Oct 1, 2013 at 16:23
  • 1
    @dav Fixed both issues without IFERROR(). I just didn't evaluate all the combinations. Did them now.
    – Jerry
    Oct 1, 2013 at 16:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .