need help with a complicate "if" formula

[sjsandborn]

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

[navic]

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)

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

[sjsandborn]

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!

[CheshireCat]

How about this:

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

Cheers,