# Thread: need help with a complicate "if" formula

1. ## need help with a complicate "if" formula

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")  Reply With Quote

2. Originally Posted by sjsandborn I'm hoping someone can help me
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)  Reply With Quote

3. 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"))))))))  Reply With Quote

4. Originally Posted by sambit 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!  Reply With Quote

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

#### Posting Permissions

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