rounding off amount 1,2,3,4 to 5 or 6,7,8,9 to 0

[chadski021]

Hello is there a way rounding off the amount if there is 1,2,3,4 or 6,7,8,9 at the end

 

for example if the amount is 191 it should be 195 or if there is 197 it should be 200

 

other example 

 

1856 to 1860

2003 to 2005

 

 

 

 

 

 

 

 

 

[LaRetta]

Hi Chad,

 

Try:

 

Ceiling ( yourNumber / 5 ) * 5

[Lee Smith]

Hi LaRetta,

 

I posted this before I saw your post,

 

This is an older calculation, but I thought it might be interesting to have a comparison.

 

 5 * (Int(num/5) + Sign(num)*(Mod(num/5;1)≠0)) 

Edited August 5, 2013 by Lee Smith
Change the post because LaRetta had posted one already.

[chadski021]

Hello I have another question about the decimal how about 287.5. This is the calculation that I used in my penalty field.

Let([

 a = date_due;                                                                   //Example Data:  5/17/2013                                        

 b = date_paid;                                                                  //Example Data:  6/17/2013

 c = GetAsNumber ( b - a );                                          //Example Data:  32

 d = If(c<1;0;c);                                                                  //Example Data:  1

 e = Monthly Installment;                                             //Example Data:  5750

 f = e * .05;                                                                          //Example Data:  287.5

 g= 5 * (Int(f/5) + Sign(f)*(Mod(f/5;1)≠0)) ;          //Example Data:  290

 h = If( g ≤ 100;100; g );                                                   //Example Data:  290

 ElapsedMonths = GetAsText(Mod(Month(beer) - Month(angel) + 12 - If(Day(beer) < Day(angel); 1; 0); 12));     //Example Data:  1

r = If(d = 0;0;h)                                                                  //Example Data:  290

 

];

 

If(ElapsedMonths = 0 ; 1 * r ;( ElapsedMonths + 1) * r) //Example Data:  580

 

)

 

This is the formula how to get the penalty =      Monthly Instalment    X     5% (0.05)

 

I need to know how many months the customer without pay and how much is the penalty for this so I used this      If(ElapsedMonths = 0 ; 1 * r ;( ElapsedMonths + 1) * r)

 

From the sample data the answer should be 575 not 580 because 5750 * 0.05 = 287.50

287.50 * 2 = 575 I get the value of the from the red text:  If(ElapsedMonths = 0 ; 1 * r ;( ElapsedMonths + 1) * r)

Any idea / comments about my calculation? Can you guys give me an idea how can I get the 575?

[LaRetta]

I would be happy to help you with the calculation but, although you gave me the result you desire, the rules are still unclear.  So in human-speak, please describe the situation similar to this:

• If client doesn't make the payment within a one-month timeframe, they pay a penalty.  
• This penalty is 5% and is rounded up to the nearest $5.  Is this true?
• If payment is late even by one day (over the 'one month' restriction) they pay 1 * the penalty.
• If payment is late by 2 months, they pay 2 * the penalty etc. ?
• What is the purpose of h?  They are always charged minimum $100?
• Your Elapsed calculation is incorrect if, for example, you change the pay date to 6/17/2014 - you show 1 and I believe you want 13, right?

... please list out the business rules.  I do not know why you are even calculating the number of days when the 'month' can be determined by the date itself.  In your above calculation, c actually equals 31 not 32 and d would be 31 and not 1 as you indicate.  Your ElapsedMonths will break if over a year.  I suggest using:  12 * ( Year ( paid ) - Year ( due ) ) + Month ( paid ) - Month ( due ) - ( Day ( paid ) < Day ( due ) )

 

I know these things can be difficult to explain and you are doing a really nice job of it, but I need to be clear on the rules first.  Why are you rounding up to 290 if you want to multiply the elapsed months * 287.50?

 

Hang in there ... we'll get it worked out.  

[chadski021]

Hello LaRetta thank you for your reply and sorry for not explaining it clearly.

These are the rules:

• If client doesn't make the payment within a one-month timeframe, they pay a penalty.  

Yes this is correct.

• This penalty is 5% and is rounded up to the nearest $5.  Is this true?

Yes this is true if the penalty is whole numbers not with decimal numbers. As my example 287.5 will become 290 is correct for 1 month penalty only. But If the penalty is more than 1 month for example 287.5 in 3 months the calculation would be 287.5 X 3(because of 3 months) = 862.5 then rounded up to the nearest $5 is equal to $865. I hope it make sense 

• If payment is late even by one day (over the 'one month' restriction) they pay 1 * the penalty.

Yes this correct.

• If payment is late by 2 months, they pay 2 * the penalty etc. ?

Yes this correct.

• What is the purpose of h?  They are always charged minimum $100?

For example the client has a penalty $99 below it should become %100 that’s why I put h

Can you help me to revise my calculation I am only new to filemaker

[LaRetta]

It will make a difference WHERE these pieces are applied.  So one more question and we should be able to nail this calc for you, Chad:

 

---

What is the purpose of h?  They are always charged minimum $100?

For example the client has a penalty $99 below it should become %100 that’s why I put h

---

 

The minimum $100 ... is that PER MONTH or is that overall?  IOW:

 

if the monthly penalty is $35 and it is one month, it would be $100

If the monthly penalty is $35 and it is for 5 months, would it be 35 * 5 = 175 so use the $175 since it is over $100?  Or would the 35 be jacked up to 100 then multiplied against the 5? 

[LaRetta]

Could the rule be condensed as follows?

 

If Customer is ONE DAY late, a 5% penalty is charged, times the number of months over (1 day still being considered 1 month) and then the penalty is rounded up to nearest $5  OR minimum of $100.

[LaRetta]

Hmmm, I'm unsure how that answers my uncertainties but I'll give it a go anyway.  Here is the calculation I believe you need:

0 +

If ( date_paid - date_due > 0 ;                   //Boolean true if any days, skipping calc if not past due

Let ( [
 install = monthly_installment ;                  //Example Data:  5750
 due = date_due ;                                 //Example Data:  5/17/2013      
 paid = date_paid ;                               //Example Data:  6/17/2013
 
 nMonths = 12 * ( Year ( paid ) - Year ( due ) ) + Month ( paid ) - Month ( due ) - ( Day ( paid ) < Day ( due ) ) + 1 ;

 monthChg = install * .05 ;                        //Example Data:  287.5
 total = nMonths * monthChg ;                      //Example Data:  1 * 287.5 = 287.5
 round5 = Ceiling ( total / 5 ) * 5                //Example Data:  290
] ;

Max ( 100 ; round5 )

)  // end let
)  // end if

Number 0

It seemed you wanted 0 if no result so I included it. If you wish 0 even if both dates are empty, also uncheck 'do not evaluate if all referenced fields are empty'. Some folks want 0 always; some want 0 if changed; some want empty and I do not know your business rules regarding 0 and null usage so adjust accordingly. Most just leave it empty. If you don't want the 0, just remove 0+ from the top.  And if you wish that the calc doesn't evaluate unless both fields have a value, we can do that as well.

 

Not Yet Paid?

I do not know your process or how you are using this calculation but what do you do if there is a Due Date but not yet a date paid? This calc says they owe $100 but several months can have passed and it can't be included because it can't be computed. Would you want to compare to the current date to see how much penalty is accumulated to that point? Or do you have other tracking methods to catch non-pays? Is this for a report or to post charges against a Customer?

 

Penalty records

It works best if each Penalty charge is a record within financials or LineItems along with Installments, Credits, Write-Offs, Discounts and Payments (and then relating back to its parent which might be Invoice, Contract, Project etc).

 

Let me know if it's missing the mark ... we can easily adjust.  

[chadski021]

Hello LaRetta Thank you so much for you time helping me. 

 

“I do not know why you are even calculating the number of days when the 'month' can be determined by the date itself.”

 

Example:

Monthly Instalment = 5,750.00

To get the penalty = Monthly Instalment X 0.05(5%)

Due Date = 5/17/2013 

Date Paid = 5/17/2013 

 

When I used this calculation:

12 * ( Year ( paid ) - Year ( due ) ) + Month ( paid ) - Month ( due ) - ( Day ( paid ) < Day ( due ) ) 

 

The answer is 0 which is correct but if I change the Date paid to 5/18/2013 it should be 1 month because

The customer should pay before 5/17/2013 or exact date 5/17/2013. If the customer is late in one day which is 5/18/2013 the customer should pay a penalty (amount is 287.5).

 

Question: “The minimum $100 ... is that PER MONTH or is that overall?”

Answer: “If the monthly penalty is $35 and it is for 5 months, would it be 35 * 5 = 175 so use the $175 since it is over $100”

 

I revise my calculation:

 

Let([

 a = date_due;

 b = date_paid;

 c = GetAsNumber ( b - a );

 d = If(c<1;0;c);

 e = Monthly Installment;

 f = e * .05;

 m = 12 * ( Year ( b ) - Year ( a ) ) + Month ( b ) - Month ( a ) - ( Day ( b ) < Day ( a ) );

 r = If(d = 0;0;f);

x = If(m  <  1 ; r * 1 ;( m + 1) * r);

g= 5 * (Int(x/5) + Sign(x)*(Mod(x/5;1)≠0)) ;

h = If( g ≤ 100;100; g )

 

];

 

h

 

)

 

Using this calculation I get the answer what I want to show. Any comment with my calculation?

[LaRetta]

Plug in my calc and compare.  your calc says they owe $100 if they pay on the exact day due.  Is that what you want?  And your calc charges if both dates are empty and if only DatePaid is empty.  My calc would be more efficient as well.

 

I should add also that I think it is easier if the declared variables fit the purpose they represent.  Otherwise, one is always having to look back up to see what g means or what f means instead of continuing to follow the flow of reading the calc.  

 

But for being new in the business, you did an excellent job translating your needs, both in explaining it and in writing one of your first calcs!!

 

ADDED:  Also so folks understand ... there was only one sentence on the prior post before.  Chad modified it after I had responded, LOL.

Edited August 7, 2013 by LaRetta

[chadski021]

Thanks LaRetta it works perfectly