Fee Schedule Calc need help please

[stann]

Hello

I have a fee schedule for buyers fees, It is like if the sale is 1 to 99 dollars then use the contents of the field "feeA" and if the sale is 100 to 200 dollars then get the fee from the field "feeB" and so on. But all the prices are now set up as fields so it needs to be

if the price is between " priceA1" and "priceA2" then the fee is "FeeA"

if the price is between " priceB1" and "priceB2" then the fee is "FeeB"

Can someone show me how to write this calc? Thanks

STANN

[RalphL]

Use a lookup. Looks can be setup to use next highest or next lowest value depending on how you set the lookup table.

example

99 FeeA

199 FeeB

299 FeeC

[The Shadow]

Case( price >= priceA1 and price <= priceA2; FeeA; price >= priceB1 and price <= priceB2; FeeB )

[stann]

Hi

I tried what you gave me but somehow it works intermittenly. Here is the formula I ended up using

So the field names are a little changed. They are as follows.

"Blockcarprice" is the price of a car

"LowerbuyerA (thru)X " Is the lower end of that schedule and

HighBuyerA (thru) X " is the high end of that schedule

Buyer ( A throughx ) is the total fee assigned for that area of the schedule

So here is the formula that I have used

Case( blockcarprice >= lowbuyerA and blockcarprice <= HighBuyerA; buyerA; blockcarprice >= lowbuyerb and blockcarprice <= HighBuyerB; buyerb; blockcarprice >= lowbuyerc and blockcarprice <= HighBuyerC; buyerc; blockcarprice >= lowbuyerd and blockcarprice <= HighBuyerD; buyerd; blockcarprice >= lowbuyere and blockcarprice <= HighBuyerE; buyere; blockcarprice >= lowbuyerf and blockcarprice <= HighBuyerF; buyerf; blockcarprice >= lowbuyerg and blockcarprice <= HighBuyerG; buyerg; blockcarprice >= lowbuyerh and blockcarprice <= HighBuyerH; buyerh; blockcarprice >= lowbuyeri and blockcarprice <= HighBuyerI; buyeri; blockcarprice >= lowbuyerj and blockcarprice <= HighBuyerJ; buyerj; blockcarprice >= lowbuyerk and blockcarprice <= HighBuyerK; buyerk; blockcarprice >= lowbuyerl and blockcarprice <= HighBuyerL; buyerl; blockcarprice >= lowbuyerm and blockcarprice <= HighBuyerM; buyerm; blockcarprice >= lowbuyern and blockcarprice <= HighBuyerN; buyern ; blockcarprice >= lowbuyero and blockcarprice <= HighBuyerO; buyero; blockcarprice >= lowbuyerp and blockcarprice <= HighBuyerP; buyerp; blockcarprice >= lowbuyerr and blockcarprice <= HighBuyerR; buyerr; blockcarprice >= lowbuyers and blockcarprice <= HighBuyerS; buyers; blockcarprice >= lowbuyert and blockcarprice <= HighBuyerT; buyert; blockcarprice >= lowbuyertu and blockcarprice <= HighBuyerU; buyeru; blockcarprice >= lowbuyertv and blockcarprice <= HighBuyerV; buyerv; blockcarprice >= lowbuyertw and blockcarprice <= HighBuyerW; buyerw; blockcarprice >= lowbuyertx and blockcarprice <= HighBuyerX; buyerx)

It works fine for the first few areas up to the "F" section and then it doesn't work.

I just don't know what to do?

Thanks for your help

STAN

[stann]

I tried this formula without the case statement but it still doesn't work after the F series. It is working but only intermittently.

This is the new formula I have tried.

If (blockcarprice ? lowbuyerA and blockcarprice ? HighBuyerA; buyerA; If(blockcarprice ? lowbuyerb and blockcarprice ? HighBuyerB; buyerb; If(blockcarprice ? lowbuyerc and blockcarprice ? HighBuyerC; buyerc; If(blockcarprice ? lowbuyerd and blockcarprice ? HighBuyerD; buyerd; If(blockcarprice ? lowbuyere and blockcarprice ? HighBuyerE; buyere; If(blockcarprice ? lowbuyerf and blockcarprice ? HighBuyerF; buyerf; If(blockcarprice ? lowbuyerg and blockcarprice ? HighBuyerG; buyerg; If(blockcarprice? lowbuyerh and blockcarprice ? HighBuyerH; buyerh; If(blockcarprice ? lowbuyeri and blockcarprice ? HighBuyerI; buyeri; If(blockcarprice? lowbuyerj and blockcarprice ? HighBuyerJ; buyerj; If(blockcarprice ? lowbuyerk and blockcarprice ? HighBuyerK; buyerk; If(blockcarprice ? lowbuyerl and blockcarprice ? HighBuyerL; buyerl; If(blockcarprice ? lowbuyerm and blockcarprice ? HighBuyerM; buyerm; If(blockcarprice? lowbuyern and blockcarprice ? HighBuyerN; buyern ; If(blockcarprice ? lowbuyero and blockcarprice ? HighBuyerO; buyero; If(blockcarprice ? lowbuyerp and blockcarprice ? HighBuyerP; buyerp; If(blockcarprice ? lowbuyerr and blockcarprice ? HighBuyerR; buyerr; If(blockcarprice ? lowbuyers and blockcarprice ? HighBuyerS; buyers; If(blockcarprice ? lowbuyert and blockcarprice ? HighBuyerT; buyert; If(blockcarprice ? lowbuyertu and blockcarprice ? HighBuyerU; buyeru; If(blockcarprice ? lowbuyertv and blockcarprice ? HighBuyerV; buyerv; If(blockcarprice ? lowbuyertw and blockcarprice ? HighBuyerW; buyerw; If(blockcarprice ? lowbuyertx and blockcarprice ? HighBuyerX; buyerx)))))))))))))))))))))))

I could really use someones' help here

Thanks

Stann

[stann]

I am so SORRY I have fixed it.

A couple fields were out of order and one was duplicated. I kept checking and checking and it is working fine now.

So sorry if I inconvienced anyone

Thanks

STANN