I got a very helpful reply here once regarding calculating shipping costs using this formula:

 

Case (
BillableWeight ≤ 1 ; 8.99 ;
BillableWeight ≤ 10 ; 8.99 + 0.5 * BillableWeight ;
9.99 + 0.4 * BillableWeight
)

 

Not however, the terms have changed and I need to evaluate charges that are BETWEEN certain values....

For instance: ≤ 1 ; 8,99 ; is no longer true...

Now it's .01 - .69 = 4.99 etc...

How would I go about modifying the calculation to evaluate between two values instead of less than a value?

The formula that you have does evaluate charges that are BETWEEN certain values. The Case() function returns the result for the first test that returns true. So you only need to arrange your boundaries in ascending order.

1 hour ago, comment said:

 So you only need to arrange your boundaries in ascending order.

Explain Like I'm 5?

 

Here's the new formula for the shipping rates...

 

0.01 - 0.69 lbs = $4.99
0.70 - 0.99 lbs = $5.79
1.00 - 8.00 lbs = $5.99 + .80 per lb

 

I understand evaluating something like ≤ .70 ; $4.99

But I'm not understanding how I would get to  >.70  ≤  .99 ; 5.79 ;

In order to evaluate the new pricing structure I have to be able to define a cost as being between two sets of weights instead of just being less than X.

Edited November 20, 20187 yr by OneStop

24 minutes ago, OneStop said:

Like I'm 5?

I am afraid you will need 2nd grade math for this - but I will try...

Consider this:

Case ( 
BillableWeight ≤ .5 ; "A" ; 
BillableWeight ≤ 1 ; "B" ; 
BillableWeight ≤ 5 ; "C" ; 
BillableWeight ≤ 10 ; "D" ; 
"E"
)

What this means is that:

• when BillableWeight is between 0 and 0.5, the result will be "A";
• when BillableWeight is between 0.5 and 1, the result will be "B";
• when BillableWeight is between 1 and 5, the result will be "C";
• when BillableWeight is between 5 and 10, the result will be "D";
• when BillableWeight is above 10, the result will be "E".

Why? Because when BillableWeight is say 4, the first test returns false: 4 is not ≤ .5. The next test,  BillableWeight ≤ 1, also returns false. The third test returns true, and the result will be "C" as expected, since 4 is between 1 and 5.

So our Case() statement does evaluate if the given input falls BETWEEN 2 boundaries; but we only need to list each boundary once (provided we do so in the correct order).

Huzzah!

Quote

Why? Because when BillableWeight is say 4, the first test returns false: 4 is not ≤ .5. The next test,  BillableWeight ≤ 1, also returns false. The third test returns true, and the result will be "C" as expected, since 4 is between 1 and 5.

This is the part that I was missing. I mistakenly thought that when the BillableWeight was say "4" in your example....That both statements of  ≤ 5 and ≤ 10 would both return true.

So is that in this calculation FMPro will just evaluate to the first true statement and then stop?

Both statements would return true - but the evaluation will never get to the second statement, because it will exit as soon as it finds one that is true. That's why the order of tests is so important.

Quote

The Case function evaluates each test expression in order, and when a True expression is found, returns the value specified in result for that expression.

(Emphasis mine)

I never really expected FMPro to handle if/then logic via SQL  syntax. The more you know I suppose.

 

Thanks for all your help.

There is no "SQL syntax" here. FMP has its own calculation engine, and it can do some pretty amazing things (and it also has some pretty annoying limitations...).