I have problem with writing calculation formula

[Ham]

Please help me, I have a problem with calculation

I want to use a formula which do some simply calculation,the functions as below, when sales amount is over $1500, there will have 5 dollars extra per hour, and once have $500(per day) Sales amount extra, will have extra 5 dollars until the sales amount reach 5000, then there will have 40 dollars extra per hour and will count extra 2 hours.

Everything seens fine but I have found a very serious problem, FM7 will take the sales amount 2,20,200,20000 as 2000, 9,90,900,90000 as more then 5000, I am very confuse, how should I write the calculation script, please help me...

I need to finish the database by this weekend!!

the calculation script as below

Case(

GetAsNumber( Sales Amount ) < "1500"; 0;

GetAsNumber( Sales Amount ) ≤ "1999"; Work Hours * 5 ;

GetAsNumber( Sales Amount ) ≤ "2499"; Work Hours * 10 ;

GetAsNumber( Sales Amount ) ≤ "2999"; Work Hours * 15 ;

GetAsNumber( Sales Amount ) ≤ "3499"; Work Hours * 20 ;

GetAsNumber( Sales Amount ) ≤ "3999"; Work Hours * 25 ;

GetAsNumber( Sales Amount ) ≤ "4499"; Work Hours * 30 ;

GetAsNumber( Sales Amount ) ≤ "4999"; Work Hours * 35 ;

GetAsNumber( Sales Amount ) ≥ "5000"; ( Work Hours + 2 ) * 40 ;

"")

Edited August 12, 2005 by Guest

[comment]

Remove the quotes around your numbers.

Note:

1. Sales Amount should be a number. If so, GetAsNumber() is not required.

2. When Sales Amount happens to be 4999.50, the person gets zilch.

[-Queue-]

You could also simplify it to something like

Let( I = Div( Div( Sales Amount; 500 ) * 500; 100 ) - 10;

If( I > 0; If( I < 40; Work Hours * I; 40 * Work Hours + 80 ); 0 )

)

[comment]

"Simplification" seems to be a relative term, JT.

In computation terms, a typical case (between 1500 and 5000, excl.) would require:

Original:

1 comparison

1 multiplication

Simplified:

2 comparisons

2 multiplications

2 integer divisions

1 substraction

Let's not mention the Let().

In file maintenance terms, if the steps and/or the associated bonus ever needed adjustment, it would take me at least half an hour (on a good day) to decipher the formula.

[-Queue-]

Okay, so for Sales Amounts up to 3499 it is not more or as efficient as it is for higher numbers, since each case in the original requires n + 1 operations, where n is the ordinal value of the test, except for the first and last, which require one fewer and one greater. The difference in average between the two solutions, though, up to 5000, is only about 13%. And if you have a higher number of larger Sales Amounts (4000 or greater), then you save 1, 2, or 3 steps with my version. So depending on what business Ham is in, it could be more efficient to use.

In file maintenance terms, if the steps and/or the associated bonus ever needed adjustment, it would take me at least half an hour (on a good day) to decipher the formula.

That's what commenting is for. ;c)

[comment]

Oops - you're right - I forgot to count the discarded comparisons. But I would take the enumerated Case() formula even if it were half as efficient as the other one. Why? Because just as sure as the sun rises, one fine day the boss will change the ranges and the sums to something that does not follow any mathematical series.

[-Queue-]

If that happened, I would then go back to the Case setup, which would be easy enough to put back in place (but I would tell the boss it will take me hours of overtime). :(

[Ham]

Thank you of all, but actually I have sort the problem according to comment's solution, actually I am a new starter, never try filemaker Pro before, and I don't have any experience on programming before, so I have try my best to get understand the script command meaning by reading the sample file, but it's not easy, actually, I don't have any script book to get understand what the script command means cuz until now I am still using the trial version, I wanna simplify my work,before i only use excel to do all the calculation but takes me lot of the time, so try to setup a database to simplify my calculation on my promoter salary...

Thanks again, but honestly, Queue's formula is too difficult to me, I don't even understand what it means, but thanks again!!