Crazy Calculation

[tmola]

Greetings, I have been working on a calculation for at least 4 days and still can't seem to make it work? I need to generate statements for work and soon! I would like to throw out the senerio and see if anyone can calculate this. If the (# of units) are 25 or less, the fee is 175.00 but if the (# of units) are between 26 and 49, the fee is now (# of units) times 7.00, if the (# of units) are between 50 and 100, the fee is now 11.80, and if the (# units) are above 101, the fee an additional 4.80 per(# units) above and beyond 101. (ie: 102 x 11.80 + 4.80 + 4.80 = 1189.60) Is there anyone that loves math challenges? (I don't! especially after 4 plus days of this!)Please send some formulas my way and I will plug them into my database and see if it works. I'm truely stumpted!

[David Jondreau]

If the (# of units) are 25 or less, the fee is 175.00 but if the (# of units) are between 26 and 49, the fee is now (# of units) times 7.00, if the (# of units) are between 50 and 100, the fee is now 11.80, and if the (# units) are above 101, the fee an additional 4.80 per(# units) above and beyond 101. (ie: 102 x 11.80 + 4.80 + 4.80 = 1189.60)

Well, it's a little unclear...the fee per unit goes up the more units you buy? Is the increase always graduated? What would 75 units cost -- 75*11.80 or 49*7 + 26*11.80?

And 102 units should be $1,208.40 (102*11.8+4.8). If I understand you correctly..

Case(

NumUnits < 25; 175;

NumUnits < 50; NumUnits * 7;

NumUnits < 102; NumUnits * 11.8;

NumUnits * 11.8 + (NumUnits-101) * 4.8

)

[comment]

This is not a math challenge, it's a lookup problem. Do not store your prices in calculation formulae - they are bound to change one day. Define a table where each record has a # of units (either top limit or bottom limit) and the corresponding price, and lookup the price from there.

[tmola]

Thanks for your help - I know it's hard to understand and I probably don't explain it too well. Since 75 units fall between the 50 and 100 mark, on paper I would calculate: (75 units x $11.80 = $885.00) If they decided to purchase 105 units, I would then charge them the (50 to 100) fee of $11.80 per unit to the maximum of 100. But since 5 extra units were purchased, I would charge an additional $4.80 per unit above the 100. On paper it would be: 100 units x $11.80 = $1,180.00 + $24.00 (the 5 extra units x $4.80)for a total balance of $1,204.00. I hope that helps. Thanks for your reply - I will try it!

[tmola]

Ok Thanks - I will try that! Therefore, I shouldn't need this huge string of commands that keeps saying "if it this, then do that", Etc... Right?

[Søren Dyhr]

Correct, you would instead need an extra table, containing a record for each threshold and use this:

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

--sd

[comment]

I would say right - before you have made your clarification. That complicates thing quite a bit.

I still say actual prices shouldn't be hard-coded into the formula, but rather looked up. However, you need all the quantities breaks and their respective prices in order to calculate the final price. And the final price is compounded, so this requires a recursive calculation.

A custom function would probably be the best tool for this, but since you don't have Advanced, I used repeating fields instead.

PriceSplitByQuantity.fp7.zip