Results 1 to 5 of 5

Thread: need help with a complicate "if" formula

  1. #1

    need help with a complicate "if" formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm hoping someone can help me. I cannot for the life of me get my formula to work. I, essentially, am trying to do the following "if cell X = No, do this, if cell value equals yes (or no is false), do this. the formula I'm currently trying is below.


    =IF(F65,"No",((IF(C77<2000000,J65,(IF(AND(C77>=2000000,C77<10000000),J66),(IF(AND(C77>=10000000,C77<25000000),J67,(IF(C77<=25000000,J68,"error"))),(IF(C77<1000000,J65,(IF(AND(C77>=1000000,C77<5000000),J66,(IF(AND(C77>=5000000,C77<10000000),J67,(IF(C77<=1000000,J68,"error")))))))))))))

    what I'm trying to do is:

    If cell F65 =No = If(C77<2000000,J65,(IF(AND(C77>=2000000,C77<10000000),J66),(IF(AND(C77>=10000000,C77<25000000),J67,(IF(C77<=25000000,J68,"error")

    If Cell F65 = yes = IF(C77<1000000,J65,(IF(AND(C77>=1000000,C77<5000000),J66,(IF(AND(C77>=5000000,C77<10000000),J67,(IF(C77<=1000000,J68,"error")

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by sjsandborn View Post
    I'm hoping someone can help me
    Help you to us, that we can help you
    Your example files may help solve the problem
    Data on the sheet before calculate
    Data on the sheet after calculate, with the results

    btw:
    =IF(A1="No",result if true,result if false)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi sjsandborn,

    I agree with navic, before raise this type of issue please attachment example file, which help to others for reply.

    Try:
    =IF(AND(F65="NO",AND(C77>0,C77<2000000)),J65,IF(AND(F65="NO",AND(C77>=2000000,C77<10000000)),J66,IF(AND(F65="NO",AND(C77>=10000000,C77<25000000)),J67,IF(AND(F65="NO",C77>=25000000),J68,IF(AND(F65="YES",AND(C77>0,C77<1000000)),J65,IF(AND(F65="YES",AND(C77>=1000000,C77<5000000)),J66,IF(AND(F65="YES",AND(C77>=5000000,C77<10000000)),J67,IF(AND(F65="YES",C77>=10000000),J68,"ERROR"))))))))

  4. #4
    Quote Originally Posted by sambit View Post
    Hi sjsandborn,

    I agree with navic, before raise this type of issue please attachment example file, which help to others for reply.

    Try:
    =IF(AND(F65="NO",AND(C77>0,C77<2000000)),J65,IF(AND(F65="NO",AND(C77>=2000000,C77<10000000)),J66,IF(AND(F65="NO",AND(C77>=10000000,C77<25000000)),J67,IF(AND(F65="NO",C77>=25000000),J68,IF(AND(F65="YES",AND(C77>0,C77<1000000)),J65,IF(AND(F65="YES",AND(C77>=1000000,C77<5000000)),J66,IF(AND(F65="YES",AND(C77>=5000000,C77<10000000)),J67,IF(AND(F65="YES",C77>=10000000),J68,"ERROR"))))))))

    That worked!!! THANK YOU SO MUCH!

  5. #5
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    How about this:

    Code:
    =IFERROR(IF(F65="No",LOOKUP(C77,{0,2000000,10000000,25000000},J65:J68),LOOKUP(C77,{0,1000000,5000000,10000000},J65:J68)),"Error")
    Cheers,

Posting Permissions

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