Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF AND OR help (Excel 2002)

    I need to have a way to flag if two or more cells out of five have values greater than 0. Assume the data is in A1, B1, C1, D1, E1. What logical formula can I put in F1 so that something is returned in F1 if at least two of the cells have values greater than 0? I know there must be a way to do this with IFs ANDs or ORs but I haven't been able to figure it out.

    Thanks in advance for any help you can provide.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IF AND OR help (Excel 2002)

    <P ID="edit" class=small>(Edited by Rudi on 02-Feb-08 09:30. Added array function!)</P>There is probably an array based formula that can shorten this expression, but for now, this formula works:
    =IF(IF(A1>0,1,)+IF(B1>0,1,0)+IF(C1>0,1,0)+IF(D1>0, 1,0)+IF(E1>0,1,0)>1,"Two or more cells are greater than zero","Passed")

    I managed to work out an array formula to do this too...Its a bit shorter!
    =IF(SUM(IF(A2:E2>0,1))>1,"Two or more cells are greater than zero","Passed")
    Please remember to confirm entry of this formula by pressing CTRL+SHIFT+ENTER!
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF AND OR help (Excel 2002)

    Wouldn't that be

    =IF(SUM(IF(A1:E1>0,1))>1,"Two or more cells are greater than zero","Passed")

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF AND OR help (Excel 2002)

    You can also use this formula; it is not an array formula, you don't have to use Ctrl+Shift+Enter to confirm it:
    <code>
    =IF(COUNTIF(A1:E1,">0")>1,"At least two positive","")
    </code>
    This formula only returns a text in F1 if at least two values are positive.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IF AND OR help (Excel 2002)

    Yep...your right.

    I was testing the formula in row two to see if it gave the same results as the "long" one, and forgot to change the references back to row one.
    Regards,
    Rudi

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Location
    Victoria, Br. Columbia, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF AND OR help (Excel 2002)

    Thanks to both of you for the solutions. This is exactly what I needed!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •