CASE function

[Database Administrator]

I have a trouble of expressing a condition within the CASE statement
when a test result should be between two values. Within a pricing
table for financial assets where an asset can have several prices, I
am to select one price when the ratio between the highest and the
lowest price is let's say between 3% and 10% and another price when
the ratio is more than 10%. I compute ratios separately and store them
in separate fields, named highlow

A statement(highlow >3 xor highlow<10) does not select the right price
neither a statement (highlow >3<10). Both statements are accepted by
the CASE formula.

What am I doing wrong? Is there another approach?

I would appreciate any help

[Database Administrator]

On 2008-11-14 08:42:51 -0800, "sovageorge@gmail.com"
said:

> I have a trouble of expressing a condition within the CASE statement
> when a test result should be between two values. Within a pricing
> table for financial assets where an asset can have several prices, I
> am to select one price when the ratio between the highest and the
> lowest price is let's say between 3% and 10% and another price when
> the ratio is more than 10%. I compute ratios separately and store them
> in separate fields, named highlow
>
> A statement(highlow >3 xor highlow<10) does not select the right price
> neither a statement (highlow >3<10). Both statements are accepted by
> the CASE formula.

I wouldn't use xor. In fact, I've *never* used xor. Perhaps that's just
my mathematical inadequacy showing.

Case( highlow > 3 and highlow < 10, Result1, DefaultResult)

should work, since you want to select between the two highlow values.

However, if you have different results below 3 and greater than 10, I'd
write it like this:

Case(

highlow =< 3, Result 1;

highlow > 3 and highlow < 10, Result2,

DefaultResult)

Maybe highlow can never be less than 3, in which case the first calc will work.

Case statements always return the first true result, so order of
arguments matters.


--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA