loop to calculate a fee

[campagnolo1]

Greetings,

I'm chasing my tail here trying to come up with a way to calculate a shipping fee.
Here is my situation: I need to calculate a fee for shipping an item. right now I have an if/else statement that checks if the cost of an order is between a certain amount (let's say 3k to 4k) and then return the shipping fee for that range. That's all fine since there are only 10 ranges that have individual fees. But if the order amount is over $5k then you add $6.25 to the last stated fee of 80.25 and then for each additional $1k you add $6.25. So if your order is between $1.6k to $5k the fee is 80.25. If the order is between $5k and $6k you add $6.25 to the 80.25 (=$86.50). If it's between $6k and $7k you add $6.25 to the $86.50.
Make sense? I hope! Now I'm trying to come up with a loop that will do this for me without having to write line after line until I get to $500,000. I'm doing baby steps at the moment, and this is where I'm at right now:
local numberVar bamount := 80.85;
local numberVar addamount = 6.25;
if Sum ({@ItemCost}) <= 20 then bamount := 0
else if Sum ({@ItemCost}) in 20 to 40 then 7.00
else if Sum ({@ItemCost}) in 40 to 100 then 19.45
else if Sum ({@ItemCost}) in 100 to 200 then 29.00
else if Sum ({@ItemCost}) in 200 to 350 then 45.60
else if Sum ({@ItemCost}) in 350 to 500 then 51.20
else if Sum ({@ItemCost}) in 500 to 750 then 58.20
else if Sum ({@ItemCost}) in 750 to 1000 then 65.15
else if Sum ({@ItemCost}) in 1000 to 1250 then 72.10
else if Sum ({@ItemCost}) in 1250 to 1600 then 76.65
else if Sum ({@ItemCost}) in 1600 to 5000 then 80.85
else if Sum ({@ItemCost}) in 5000 to 6000 then bamount := bamount + addamount
else if Sum ({@ItemCost}) in 6000 to 7000 then bamount := bamount + (addamount*2)
else if Sum ({@ItemCost}) in 7000 to 8000 then bamount := bamount + (addamount*3)
else if Sum ({@ItemCost}) in 8000 to 9000 then bamount := bamount + (addamount*4)
else if Sum ({@ItemCost}) in 9000 to 10000 then bamount := bamount + (addamount*5)
else if Sum ({@ItemCost}) in 10000 to 11000 then bamount := bamount + (addamount*6)
else if Sum ({@ItemCost}) in 12000 to 13000 then bamount := bamount + (addamount*7)

I know this is not a loop, but I can't figure out how to structure the loop.
Any help is greatly appreciated!

Chris

 

[CRCRCR]

Very quickly, without thinking too much about it, but

can't the

*2, *3, *4 etc...

be derived from (Itemcost - 5000)\1000 '' "\" = integer divide)

you may have to tinker with it a little, like adding 1 to the result.

k.

 

[crystalkiwibruce]

The approach I'd recommend is to create a column in Excel with a long list of values and experiment with various formulas to calculate the various conditions.

That will help test your logic and you can then convert that into a Crystal formula.

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[campagnolo1]

Thanks for the responses!
I will try and fiddle around in Excel a bit today and see if I can come up with anything. I think I need to try and separate things a bit and then try to put it all back together once I have made all the calculations. I have a feeling that trying to do it all at once will not get me anywhere.
I'll keep you posted but will still take any advice I can get!

;o)

 

[campagnolo1]

So this is what I came up with this morning:
shared NumberVar value := Sum ({@ItemCost});
shared NumberVar minamount := 5000.01;
shared NumberVar maxamount := 6000;
shared NumberVar amountincrease := 1000;
shared NumberVar fee := 80.25;
shared NumberVar feeincrease := 6.25;
shared NumberVar increaseindex := 1;

if Sum ({@ItemCost}) in 0 to 20 then fee := 0
else if value in 20.01 to 40 then fee := 7
else if value in 40.01 to 100 then fee := 19.45
else if value in 100.01 to 200 then fee := 29.00
else if value in 200.01 to 350 then fee := 45.60
else if value in 350.01 to 500 then fee:= 51.20
else if value in 500.01 to 750 then fee := 58.20
else if value in 750.01 to 1000 then fee := 65.15
else if value in 1000.01 to 1250 then fee := 72.10
else if value in 1250.01 to 1600 then fee := 76.65
else if value in 1600.01 to 5000 then fee := 80.85
else
(
For minamount := 5000 to 500000 Do
(
if value in minamount to maxamount
then fee := fee + (feeincrease*increaseindex);

minamount := minamount + amountincrease;
maxamount := maxamount + amountincrease;
increaseindex := increaseindex+1;
);
fee
)
It works for the first part of the formual up to $5,000. I have to create some dummy orders to test the loop, but I'm hopeful that it works.
Stay tuned,

Chris

 

[campagnolo1]

Works!

Thanks everyone, I guess I solved it.