Complex Calc Help

[Serenity]

Hi,

I am building a duty calculator and have attempted it but it's stack full of multiple fields and multiple case statements. I am unfamiliar with let statements/custom functions but wondering if they would be of value to use, and if so, how.

When the following protocol is assesed it is on the "fee"

EXAMPLE 1

fee = 15,000 (rate 1 applies)

15,000/100 = 150; 150*1.5=$225

EXAMPLE 2

fee = 156,000 (rate 4 applies)

2350 + ((56,000/100)* 3.25) = $4170

[color:purple]RATES

[color:purple]Not more than $20,000

$1.50 for every $100.00 or part $100.00

More than $20,000 but not more than $50,000

$300.00 + $2.25 for every $100.00 or part $100.00 over $20,000

More than $50,000 but not more than $100,000

$975.00 + $2.75 for every $100.00 or part $100.00 over $50,000

More than $100,000 but not more than $250,000

$2350.00 + $3.25 for every $100.00 or part $100.00 over $100,000

More than $250,000 but not more than $500,000

$7225.00 + $3.50 for every $100.00 or part $100.00 over $250,000

More than $500,000 but not more than $700,000

$15975.00 + $4.00 for every $100.00 or part $100.00 over $500,000

More than $700,000

$23975.00 + $4.50 for every $100.00 or part $100.00 over $700,000

So I was thinking this sort of thing

[color:red]FiguresDutyMultiplier[/color]

Case(FiguresFee  ≥ 20000; Int(FiguresFee/100); FiguresFee>20000 and FiguresFee  ≤ 50000; FiguresFee-20000)



[color:red]Duty[/color]

Case(FiguresFee <=20000; 1.5*FiguresDutyMultiplier; 



FiguresFee >20000 and FiguresFee<=50000; 300+2.25*FiguresDutyMultiplier;1)

TIA

Edited July 16, 2007 by Guest

[Genx]

Well case statements terminate when they evaluate to true so you could really just do:

Case( FiguresFee <=20000 ; do x ;

FiguresFee <=50000 , do y;

FiguresFee <=100000 ; do z ;

FiguresFee <=250000 ; do next one etc...

)

[The Shadow]

The Let() function allows you to create temporary values you can access later in the calculation.

If you're worried that this table will be changing, or the formula, I would suggest breaking them out to make modifications easier:

Let( [ 

         /* determine the rate to use */

         values = Case( amount > 700000; List( 700000; 23975; 4.5 );

                       amount > 500000; List( 500000; 15975; 4 );

                       amount > 250000; List( 250000; 7225; 3.5 );

                       amount > 100000; List( 100000; 2350; 3.25 );

                       amount > 50000; List( 50000; 975; 2.75 );

                       amount > 20000; List( 20000; 300; 2.25 );

                     /* default */   List( 0; 0; 1.5 )

                         );



        baseAmount = GetAsNumber( GetValue( values; 1 ) );

        fixedCharge = GetAsNumber( GetValue( values; 2 ) );

        incrRate = GetAsNumber( GetValue( values; 3 ) )

      ];



  /* compute the duty */

  fixedCharge + (incrRate * Int( (amount - baseAmount) / 100 ))

)

List()/GetValue() was new to 8.5, if you're only using 8.0 you can work around it using MiddleValues().

Needless to say, if you're not comfortable with what is going on here you might not want to use it in your solution, as you're going to have to fix the bugs in it. :

[comment]

It's not a good idea to hard-code actual rates into the calculation formula. Rates may change, and it should be possible for the user to change them without requiring any developer privileges or skills. More importantly: if you change the rates in the calculation formula itself, all your existing records are going to recalculate and you will lose your entire history.

The rates should be looked up from a related table, where each record has a LowLimit, Base and Extra fields. The relationship should match Fee to Rates::LowLimit, and the lookup should be using the option to 'copy next lower' if no exact match.

Once you have looked up the LowLimit, Base and Extra figures, the calculation can be simply:

Base + Extra * Ceiling ( ( Fee - LowLimit ) / 100 )

[Serenity]

Thanks, I'll review your responses and try to implement them.

Comment - the rates are generally in place for a couple of years, and this database is only required till settlement which will be within the next month so it varying isn't an issue. :

Edited July 16, 2007 by Guest

[comment]

It requires no more effort, possibly even less, to do it right.

[Søren Dyhr]

No just:

http://www.filemaker.com/help/FunctionsRef-316.html

--sd

[Serenity]

There really is absolutely no need for such snarkiness.

[comment]

There is no need to read an imaginary attitude into my post - or to get personal at all. I have pointed out that hard-coding data into calculations is not good practice. Your reply implies there might be some advantage in departing from such practice in your case. I don't think so - and that's all I said.

[Vaughan]

I think you're TOO POLITE, Comment.

Hardcoding data into calculations SUX BIG TIME. But let Brainiac find out for themself. We all did.

LOL I'm kidding of course. (Not about the SUX BIG Time though.)