CASE function

[Joe_Phoenix]

Am I using the right calculation for this senario...It won't work

Case( Hourly Rate < 65;"15";Hourly Rate ≥ 65 and Hourly Rate < 80;"16" ; Hourly Rate ≥ 80 and Hourly Rate < 100;"17" ; Hourly Rate ≥ 100 and Hourly Rate < 125;"20")

[IdealData]

You haven't said WHY it doesn't work but I can see 2 reasons

1. Hourly Rate > 125

as you have not specified a default value at the end of your clauses.

Case(

Hourly Rate < 65;"15";

Hourly Rate ≥ 65 and Hourly Rate < 80;"16" ;

Hourly Rate ≥ 80 and Hourly Rate < 100;"17" ;

Hourly Rate ≥ 100 and Hourly Rate < 125;"20";

"25")

)

2. The target field is not a TEXT field - do you mean it to be a NUMBER field?

Case(

Hourly Rate < 65;15;

Hourly Rate ≥ 65 and Hourly Rate < 80;16 ;

Hourly Rate ≥ 80 and Hourly Rate < 100;17 ;

Hourly Rate ≥ 100 and Hourly Rate < 125;20;

25

)

[Joe_Phoenix]

Hi,

Thanks for your reply.

1. When I enter a value below 65 I get the resul of 15, when I enter a result between 65 and 79 I get the result of 16 although anything above 79 I get a 0 result. I would like to get the following reults

less then 65 = 15

65-79 = 16

80-99 = 17

100-125 = 20

I get the first two results correct just not after that.

I have modified the text to numbers and added a default value of 0 as suggested.

[comment]

Your original formula should work as intended - however, Hourly Rate must be a Number field. Otherwise the comparison is evaluated using alphabetic order, and "100" comes before "80".

BTW, you have no result for Hourly Rate 125 and above. Assuming that the highest possible Hourly Rate is 124, you could simplify the formula to:

Case (

Hourly Rate < 65 ; 15 ;

Hourly Rate < 80 ; 16 ;

Hourly Rate < 100 ; 17 ;

20

)

The Case () function returns the result of the FIRST true test, so there's no need to check the lower limit.

[Joe_Phoenix]

thankyou for you help...all fixed