I need to calculate a commission value from a currency field called "amount".

Over the first 2500 the commission is 15% with a minimum of 40.

Over the next 2500 the commission is 10%

Over the next 5000 the commission is 5%

Over the next 190000 the commission is 1%

Over the remaining amount the commission is 0,5 % with a maximum of 6775.

I am stuck

Help is rewarded with a LinkedIn endorsement or enternal friendship!

Dick

The calculation itself could be fairly simple - see a somewhat similar example here:

http://fmforums.com/...lf/#entry394693

However, I would worry about hardcoding the ranges and the associated rates and extrema, as these are inevitably bound to change. What's worse, even the number of ranges could change - so this would suggest either auto-entering the result of a custom recursive function, or using a looping script to write it out.

Why do you recommend a recursive CF or looping script rather than just a regular custom function?

As I said, the number of ranges could change. I believe you need to calculate the sub-commission for each range. I don't see how you could do that without recursing/iterating/looping.

Could you clarify the formula for us a bit? If the amount is $6000, should the commission be:

6000 * .05 = 300

... or:

2500 * .15 + ( 5000 - 2500 ) * .1 + ( 6000 - 5000 ) * .05 = 675

I'll let OP answer that, but note that:

2500 * 15% > 2501 * 10%

so that the first option is rather absurd.

Touche, comment.

MetDick, I welcome LinkedIn endorsements, and this seemed like a fun exercise, so I made a quick demo file of the second calculation method with both script and custom function formats.

Commission.zip

I loaded this post last night when post #5 was the most recent, then forgot to refresh it when I read it this morning, so hadn't seen the last two posts before I wrote this calc...

Max(

Min( 2500 ; amount ) * .15 ;

40

)



+ Max(

Min( 2500 ; amount - 2500 ) ;

0

) * .1

+ Max(

Min( 5000 ; amount - 2500 - 2500 ) ;

0

) * .05

+ Max(

Min( 190000 ; amount - 2500 - 2500 - 5000 ) ;

0

) * .01



+ Min(

Max( amount - 2500 - 2500 - 5000 - 190000 ; 0 ) * .005 ;

6775

)

MetDick, you'll find there are many ways do accomplish a task in FileMaker. Comparing my calc to Jeremy Bante's: his is much easier to edit, but mine creates fewer dependencies (easier to add to your database).

What dependencies are you referring to, Dan? The custom function and the script versions of the calculation I made don't reference any other functions or scripts that aren't built-in to FileMaker, which makes them dependency-free by my reckoning. I even refrained from using my preferred custom functions for name-value pair script parameters to keep them that way. Copy-and-paste either the script or the custom function into a different file, and they just work. Is there a different idea of what code dependencies are that I'm missing?

The file I posted does include some extra fields to store the different parameters of the commission calculation and a script to set the calculated values to display fields, but only for the sake of demonstration. The script and the function are both dependent on being supplied those parameters to function correctly, which I suppose is a dependency of sorts, but not what I normally think of when I talk about code dependencies. The parameters can come from any source MetDick wants; he doesn't have to use the same global repeating fields to specify the commission brackets or the same looping script to set the calculated values into fields. MetDick could just as easily use string literals for parameters:

CommissionBracketed (

amount;

"2500¶5000¶10000¶200000";

".15¶.1¶.05¶.01¶.005";

40;

6775

)

Jeremy, I only glanced at your attached file so I hadn't realized the fields were only used to hold the values that get passed to the script or the custom function. The dependencies I was referring to were the fields, and as you pointed out, those aren't actually required, so I was wrong about that.

In that case, the custom function you wrote seems ideal - it's much easier to modify the rates/cut-off points than my calculation.