Home > Enterprise >  SQL syntax to excel formula
SQL syntax to excel formula

Time:08-11

CASE WHEN var_1 IS NULL THEN 1 
     WHEN var_1 <2 THEN -3 
     WHEN var_1 <4 AND var_1>=2 THEN -1 
     WHEN (var_1 <8 AND var_1>=4) OR (pixel_1<2.5 AND var_1>=4) THEN 1 
     WHEN var_1 >=8 AND pixel_1>=2.5 THEN 2
     ELSE 0 END

I have this sql query above, and I'am trying to convert it into excel formula. So far I have tried this, and it does not seems to be right.

=IF(FA2="";1;IF(FA2<2;-3;IF(AND(FA2<4;FA2>=2);-1;IF(OR(AND(FA2<8;FA2>=4);AND(CU2<2,5;FA2>=4));IF(AND(FA2>=8;CU2=2,5);2;0)))))

Can anyone help, please?

CodePudding user response:

So, as an example of testing each expression I did this:

enter image description here

You can now repeat that for each expression so each works correctly then you can combine them. Just a case of testing the logic.

Note OR() will return True when any of its arguments is True. Make sure you check for , and ; as this is based on system settings.

CodePudding user response:

To know if a cell is NULL you can use the ISBLANK() function.

=IF(ISBLANK(FA2);1;IF(FA2<2;2;IF(AND(FA2<4;FA2>=2);3;IF(OR(AND(FA2<8;FA2>=4);AND(CU2<2,5;FA2>=4));4;IF(AND(FA2>=8;CU2>=2,5);5;6)))))

Also you missed the >= statement for the second pixel_1 comparation.

  • Related