# 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")

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)

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"))))))))

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!

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
•