Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel Multiple IF/AND/OR conditions

Pretty simple questions but i couldn't find out how it works and i've search almost a week for an answer now.

Example:

/ One Number is above 0 and one below

  • A1: 1
  • B1: -1

/ Both Numbers are below 0

  • A1: -1
  • B1: -2

/ Both Numbers are above 0

  • A1: 9
  • B1: 2

So i want to check 3 or 4 things.

  • A1 and B1 are below 0 = return "below"
  • A1 and B1 are === 0 or above 0 return "above"
  • A1 is below 0 and B1 is above 0 return "above and below"
  • A1 is above 0 and B1 is below 0 return "above and below"
like image 325
Harry Avatar asked Dec 18 '22 08:12

Harry


1 Answers

    =CHOOSE(1+(B25<0)+(C25<0), "both above", "above and below", "both below")

I used B25 and C25 because from your comment I saw that you want to reference these cells.

If you want to distinguish cases "below and above" from "above and below", use this:

=IF(AND(B25<0,C25<0),"both below", IF(B25<0,"below and above",
     IF(C25<0,"above and below","both above")))

p.s.

Your formula (that you posted in the comment) was almost correct but missed some parentheses. It could be fixed like this:

=IF(AND(B25<0,C25<0),"both below",
   IF(AND(B25>=0,C25>=0),"both above",
     IF(AND(B25<0,C25>=0),"below and above",
       IF(AND(B25>=0,C25<0),"above and below"))))
like image 90
A.S.H Avatar answered Dec 31 '22 17:12

A.S.H