AnsweredAssumed Answered

Issues with Case Statement

Question asked by simonmurphy on Aug 14, 2018
Latest reply on Aug 15, 2018 by wimdecorte

Hi,

 

I've been trying to use a case statement to calculate tax on a gross wage value, but it's got me completely stumped as it's not giving the results I would expect. Hoping someone can notice something obvious I'm just not seeing.

Below is how I've got it setup as a custom function with gross the parameter passed to it. I've left out the actual tax calculation and replaced it with B1 - B8 for simplicity of testing.

 

Case (

gross < "355" ; "B1" ;

gross < "422" ; "B2" ;

gross < "528" ; "B3" ;

gross < "711" ; "B4" ;

gross < "1282" ; "B5" ;

gross < "1730" ; "B6" ;

gross < "3461" ; "B7" ;

gross ≥ "3461" ; "B8" ;

"Unexpected" )

 

Basically what happens is it works ok for values up to 710, but once I enter 711 and above it returns incorrect values. From 711 to 999 instead of B5 it returns B8. 1000 to 3549 returns B1 and 3550 returns B2.

 

I've also tried to implement it as an if statement, but had similar results and I would have thought a case statement was a better choice.

 

If ( gross < "355" ;

     "B1" ;

     If ( gross ≥ "355" and gross < "422" ;

          "B2" ;

          If ( gross ≥ "422" and gross < "528" ;

               "B3" ;

               If ( gross ≥ "528" and gross < "711" ;

                    "B4" ;

                    If ( gross ≥ "711" and gross < "1282" ;

                         "B5" ;

                         If ( gross ≥ "1282" and gross < "1730" ;

                              "B6" ;

                              If ( gross ≥ "1730" and gross < "3461" ;

                                   "B7" ;

                                   If ( gross ≥ "3461" ;

                                        "B8"

))))))))

 

Any help anyone can give would be much appreciated.

 

Thanks

Simon

Outcomes