Jump to content
  • 0
Sign in to follow this  
PaulDarensbourg

IF/AND STATEMENT

Question

I am trying to build a formula in Caspio that will offer the same results as excel. Below is an example of the formula from excel but I am unable to determine how to correctly write this in Caspio. Below my excel formula is what I am attempting to use in replacement but getting an error code. I am brand new to caspio, can someone offer a solution?

Thanks in advance

Caspio Error Code: 

alert_error.png?a=18.1
Invalid formula: Incorrect syntax near (.

Excel Formula:

=IF(AND(J2=7,I2<3000),50,IF(AND(J2=14,I2<3000),75,IF(AND(J2=28,I2<3000),125,IF(AND(J2=42,I2<3000),165,IF(AND(J2=56,I2<3000),225,IF(AND(J2=7,I2>=3000,I2<8000),65,IF(AND(J2=14,I2>=3000,I2<8000),125,IF(AND(J2=28,I2>=3000,I2<8000),175,IF(AND(J2=42,I2>=3000,I2<8000),215,IF(AND(J2=56,I2>=3000,I2<8000),325,IF(AND(J2=7,I2>=8000,I2<13000),85,IF(AND(J2=14,I2>=8000,I2<13000),150,IF(AND(J2=28,I2>=8000,I2<13000),225,IF(AND(J2=42,I2>=I2>=8000,I2<13000),285,IF(AND(J2=56,I2>=8000,I2<13000),425,IF(AND(J2=7,I2>=13000,I2<20999),110,IF(AND(J2=14,I2>=I2>=13000,I2<20999),175,IF(AND(J2=28,I2>=13000,I2<20999),275,IF(AND(J2=42,I2>=13000,I2<20999),375,IF(AND(J2=56,I2>=13000,I2<20999),525,IF(AND(J2=7,I2>=21000,I2<25000),135,IF(AND(J2=14,I2>=I2>=I2>=21000,I2<25000),250,IF(AND(J2=28,I2>=21000,I2<25000),325,IF(AND(J2=42,I2>=I2>=21000,I2<25000),450,IF(AND(J2=56,I2>=I2>=21000,I2<25000),625)))))))))))))))))))))))))

Attempted Caspio Replacement:

=IF(AND([@FIELD:TERM]=7,[@FIELD:AMOUNT]<3000),50,IF(AND([@FIELD:TERM]=14,[@FIELD:AMOUNT]<3000),75,IF(AND([@FIELD:TERM]=28,[@FIELD:AMOUNT]<3000),125,IF(AND([@FIELD:TERM]=42,[@FIELD:AMOUNT]<3000),165,IF(AND([@FIELD:TERM]=56,[@FIELD:AMOUNT]<3000),225,IF(AND([@FIELD:TERM]=7,[@FIELD:AMOUNT]>=3000,[@FIELD:AMOUNT]<8000),65,IF(AND([@FIELD:TERM]=14,[@FIELD:AMOUNT]>=3000,[@FIELD:AMOUNT]<8000),125,IF(AND([@FIELD:TERM]=28,[@FIELD:AMOUNT]>=3000,[@FIELD:AMOUNT]<8000),175,IF(AND([@FIELD:TERM]=42,[@FIELD:AMOUNT]>=3000,[@FIELD:AMOUNT]<8000),215,IF(AND([@FIELD:TERM]=56,[@FIELD:AMOUNT]>=3000,[@FIELD:AMOUNT]<8000),325,IF(AND([@FIELD:TERM]=7,[@FIELD:AMOUNT]>=8000,[@FIELD:AMOUNT]<13000),85,IF(AND([@FIELD:TERM]=14,[@FIELD:AMOUNT]>=8000,[@FIELD:AMOUNT]<13000),150,IF(AND([@FIELD:TERM]=28,[@FIELD:AMOUNT]>=8000,[@FIELD:AMOUNT]<13000),225,IF(AND([@FIELD:TERM]=42,[@FIELD:AMOUNT]>=[@FIELD:AMOUNT]>=8000,[@FIELD:AMOUNT]<13000),285,IF(AND([@FIELD:TERM]=56,[@FIELD:AMOUNT]>=8000,[@FIELD:AMOUNT]<13000),425,IF(AND([@FIELD:TERM]=7,[@FIELD:AMOUNT]>=13000,[@FIELD:AMOUNT]<20999),110,IF(AND([@FIELD:TERM]=14,[@FIELD:AMOUNT]>=[@FIELD:AMOUNT]>=13000,[@FIELD:AMOUNT]<20999),175,IF(AND([@FIELD:TERM]=28,[@FIELD:AMOUNT]>=13000,[@FIELD:AMOUNT]<20999),275,IF(AND([@FIELD:TERM]=42,[@FIELD:AMOUNT]>=13000,[@FIELD:AMOUNT]<20999),375,IF(AND([@FIELD:TERM]=56,[@FIELD:AMOUNT]>=13000,[@FIELD:AMOUNT]<20999),525,IF(AND([@FIELD:TERM]=7,[@FIELD:AMOUNT]>=21000,[@FIELD:AMOUNT]<25000),135,IF(AND([@FIELD:TERM]=14,[@FIELD:AMOUNT]>=[@FIELD:AMOUNT]>=[@FIELD:AMOUNT]>=21000,[@FIELD:AMOUNT]<25000),250,IF(AND([@FIELD:TERM]=28,[@FIELD:AMOUNT]>=21000,[@FIELD:AMOUNT]<25000),325,IF(AND([@FIELD:TERM]=42,[@FIELD:AMOUNT]>=[@FIELD:AMOUNT]>=21000,[@FIELD:AMOUNT]<25000),450,IF(AND([@FIELD:TERM]=56,[@FIELD:AMOUNT]>=[@FIELD:AMOUNT]>=21000,[@FIELD:AMOUNT]<25000),625)))))))))))))))))))))))))

 

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Got my answer! Figured I would post if anyone is looking for a similar situation. 

Case
WHEN ([@field:Ext_2_Term]=7) AND ([@FIELD:AMOUNT]<3000) THEN 50
WHEN ([@field:Ext_2_Term]=14) AND ([@FIELD:AMOUNT]<3000) THEN 75
WHEN ([@field:Ext_2_Term]=28) AND ([@FIELD:AMOUNT]<3000) THEN 125
WHEN ([@field:Ext_2_Term]=42) AND ([@FIELD:AMOUNT]<3000) THEN 165
WHEN ([@field:Ext_2_Term]=56) AND ([@FIELD:AMOUNT]<3000) THEN 225
WHEN ([@field:Ext_2_Term]=7) AND ([@FIELD:AMOUNT]>=3000) AND ([@FIELD:AMOUNT]<8000) THEN 65 WHEN ([@field:Ext_2_Term]=14) AND ([@FIELD:AMOUNT]>=3000) AND ([@FIELD:AMOUNT]<8000) THEN 125
WHEN ([@field:Ext_2_Term]=28) AND ([@FIELD:AMOUNT]>=3000) AND ([@FIELD:AMOUNT]<8000) THEN 175
WHEN ([@field:Ext_2_Term]=42) AND ([@FIELD:AMOUNT]>=3000) AND ([@FIELD:AMOUNT]<8000) THEN 215
WHEN ([@field:Ext_2_Term]=56) AND ([@FIELD:AMOUNT]>=3000) AND ([@FIELD:AMOUNT]<8000) THEN 325
WHEN ([@field:Ext_2_Term]=7) AND ([@FIELD:AMOUNT]>=8000) AND ([@FIELD:AMOUNT]<13000) THEN 85
WHEN ([@field:Ext_2_Term]=14) AND ([@FIELD:AMOUNT]>=8000) AND ([@FIELD:AMOUNT]<13000) THEN 150
WHEN ([@field:Ext_2_Term]=28) AND ([@FIELD:AMOUNT]>=8000) AND ([@FIELD:AMOUNT]<13000) THEN 225
WHEN ([@field:Ext_2_Term]=42) AND ([@FIELD:AMOUNT]>=8000) AND ([@FIELD:AMOUNT]<13000) THEN 285
WHEN ([@field:Ext_2_Term]=56) AND ([@FIELD:AMOUNT]>=8000) AND ([@FIELD:AMOUNT]<13000) THEN 425
WHEN ([@field:Ext_2_Term]=7) AND ([@FIELD:AMOUNT]>=13000) AND ([@FIELD:AMOUNT]<20999) THEN 110
WHEN ([@field:Ext_2_Term]=14) AND ([@FIELD:AMOUNT]>=13000) AND ([@FIELD:AMOUNT]<20999) THEN 175
WHEN ([@field:Ext_2_Term]=28) AND ([@FIELD:AMOUNT]>=13000) AND ([@FIELD:AMOUNT]<20999) THEN 275
WHEN ([@field:Ext_2_Term]=42) AND ([@FIELD:AMOUNT]>=13000) AND ([@FIELD:AMOUNT]<20999) THEN 375
WHEN ([@field:Ext_2_Term]=56) AND ([@FIELD:AMOUNT]>=13000) AND ([@FIELD:AMOUNT]<20999) THEN 525
WHEN ([@field:Ext_2_Term]=7) AND ([@FIELD:AMOUNT]>=21000) AND ([@FIELD:AMOUNT]<25000) THEN 135
WHEN ([@field:Ext_2_Term]=14) AND ([@FIELD:AMOUNT]>=21000) AND ([@FIELD:AMOUNT]<25000) THEN 250
WHEN ([@field:Ext_2_Term]=28) AND ([@FIELD:AMOUNT]>=21000) AND ([@FIELD:AMOUNT]<25000) THEN 325
WHEN ([@field:Ext_2_Term]=42) AND ([@FIELD:AMOUNT]>=21000) AND ([@FIELD:AMOUNT]<25000) THEN 450
WHEN ([@field:Ext_2_Term]=56) AND ([@FIELD:AMOUNT]>=21000) AND ([@FIELD:AMOUNT]<25000) THEN 625

END

Share this post


Link to post
Share on other sites
  • 0

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...