1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Conditional formula

Discussion in 'Business Applications' started by fitzowusu, Feb 23, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. fitzowusu

    fitzowusu Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    6
    pls i need help on this formula in Excel
    I want to create a formula to suite a criteria that i am using to diagnose metabolic syndrome but i get stuck any time i input the formula.
    this is the argument:
    B2=1.4
    M2=95
    D2=1.74
    P2=137
    Q2=89
    F2=10.3
    if B2 is less than 1.2, D2 is greater than 1.8 and M2 is greater than 85, then we conclude positive
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    so do you want to add positive to a cell
    so in the cell you want to display positive or negative put
    Code:
    =IF( AND(B2>1.2, D2>1.8, M2>85), "Positive", "Negative")
    Note - this does not include = or greater than so if you want = change to

    Code:
    =IF(AND(B2>=1.2,D2>=1.8,M2>=85),"Positive","Negative")
    if you want a conditional format - so the cell changs colour or other format - let me know

    just to check
    All three need to be higher then threshold - or just one ?

    see attached D5 has > or =
    D6 has just >
     

    Attached Files:

  3. fitzowusu

    fitzowusu Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    6
    pls i really appreciate this help u are giving me but if i encounter any other problem i will let u know.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    your welcome (y) if that all works out for you -
    You can mark your own threads solved using the [​IMG] button at the top of the page of the thread in the upper left corner. :)
     
  5. fitzowusu

    fitzowusu Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    6
    Please this is the whole situation:
    Lets say formula

    A=IF(AND(B12<1.29,D12>=1.7,M12>80),"Positive","Negative")
    B=IF(AND(R21>=215,D2>=1.7,M2>80),"Positive","Negative")
    C=IF(AND(M2>80,F2>5.6,D2>=1.7),"Positive","Negative")
    D=IF(AND(B2<1.29,R2>=215,M2>80),"Positive","Negative")
    E=IF(AND(B2<1.29,F2>5.6,M2>80),"Positive","Negative")
    F=IF(AND(F2>5.6,R2>=215,M2>80),"Positive","Negative")

    now i want to combine these conditions to make one diagnosis as MetS+ or MetS-
    thus
    IF the results of either one or more of the formulas above is positive, then the final result is MetS+ or if none of the result is positive, then the result is MetS -
     

    Attached Files:

  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    so they are all OR

    =IF( OR(
    AND(B12<1.29,D12>=1.7,M12>80),
    AND(R21>=215,D2>=1.7,M2>80),
    AND(M2>80,F2>5.6,D2>=1.7),
    AND(B2<1.29,R2>=215,M2>80),
    AND(B2<1.29,F2>5.6,M2>80),
    AND(F2>5.6,R2>=215,M2>80)
    ),
    "MetS+",
    "MetS-")

    Code:
    =IF(OR(AND(B12<1.29,D12>=1.7,M12>80),AND(R21>=215,D2>=1.7,M2>80),AND(M2>80,F2>5.6,D2>=1.7),AND(B2<1.29,R2>=215,M2>80),AND(B2<1.29,F2>5.6,M2>80),AND(F2>5.6,R2>=215,M2>80)),"MetS+","MetS-")
    
     
  7. fitzowusu

    fitzowusu Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    6
    (y)(y)Thank u very much the first formula worked but
    i ALSO USED THIS FOR THE SAME DATA ON THE ATTACHED FILE BUT I GOT name error::rolleyes:

    =IF(OR(AND(M2>80,D2>=1.7,B2&#706;1.3),
    AND(M2>80,D2>=1.7,F2>5.6),
    AND(M2>80,D2>=1.7,R2>=215),
    AND(M2>80,B2&#706;1.3,R2>=215),
    AND(M2>80,B2&#706;1.3,F2>5.6),
    AND(M2>80,R2>=215,F2>5.6),
    AND(D2>=1.7,B2&#706;1.3,R2>=215),
    AND(D2>=1.7,B2&#706;1.3,F2>5.6),
    AND(D2>=1.7,F2>5.6,R2>=215),
    AND(B2&#706;1.3,F2>5.6,R2>=215)
    ),"MetS+","MetS-")

    Pls can u help me to know what is wrong here?
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    for some reason the first expression returns #name
    AND(M2>80,D2>=1.7,B2<1.3)

    i typed it in again and copied into the main formula and it worked

    it seems to have an issue with the B2 part

    this now works
    Code:
    =IF(OR(AND(M2>80,D2>=1.7,B2<1.3),AND(R21>=215,D2>=1.7,M2>80),AND(M2>80,F2>5.6,D2>=1.7),AND(B2<1.29,R2>=215,M2>80),AND(B2<1.29,F2>5.6,M2>80),AND(F2>5.6,R2>=215,M2>80)),"MetS+","MetS-")
    strange no idea what part it did not like
     
  9. fitzowusu

    fitzowusu Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    6
    =IF(OR(AND(B3<1.29,D3>=1.7,M3>80),AND(M3>80,D3>=1.7,F3>5.6),AND(M3>80,D3>=1.7,R3>=215),AND(B3<1.3,R3>=215,M3>80),AND(M3>80,B3<1.3,F3>5.6),AND(F3>5.6,R3>=215,M3>80),AND(B3<1.3,R3>=215,D3>=1.7),AND(D3>=1.7,B3<1.3,F3>5.6),AND(D3>=1.7,F3>5.6,R3>=215),AND(B3<1.3,F3>5.6,R3>=215)),"MetS+","MetS-")
    i used this formula and it worked
    i only changed all the ">" signs with an existing one from the previous formula and it worked.
    it might have been that the sign was not recognized by excel. so using an already existing one from previous formula worked.
    THANK YOU VERY MUCH FOR YOUR SUPPORT.
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    is this a typo i introduced
     
  11. fitzowusu

    fitzowusu Thread Starter

    Joined:
    Feb 23, 2013
    Messages:
    6
    no, it might have been from me.
    But how is that possible?
     
  12. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    just seen it was < here post #7
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Conditional formula
  1. Squashman
    Replies:
    5
    Views:
    251
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090679

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice