IF/AND STATEMENT

[PaulDarensbourg]

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: 

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

 

[PaulDarensbourg]

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

[Aether]

Hello @PaulDarensbourg, you can also check this documentation for the list of calculation or formula you can use inside Caspio:
https://howto.caspio.com/function-reference/

https://howto.caspio.com/datapages/datapage-components/calculated-values/

https://howto.caspio.com/tables-and-views/data-types/formula-fields/

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/