Skip to main content
J
John.Pryal1-Visitor
1-Visitor
March 1, 2011
Solved

Question on relations

  • March 1, 2011
  • 2 replies
  • 2574 views

Hi all,

i have a simple relation d8=p1*p2, where p1=206 & p2=1.0065 making d8=207.339

ok, here is my problem, i want this value to be exactly 207.3400, but rounded to 2 decimal places, so 207.34, now i know about floor & ceil, if i write the realtion with floor (,2), then i get 207.33 (exactly) & if i write the relation with ceil (,2), then i get 207.34 (exactly). So what is the problem you ask, i have the required value of 207.34, but the next time d8 might equal 169.229, floored would give 169.22, but i would require 169.23. Once the relation is written, it is written, i don't want to get into a situation where i have to keep swapping between floor & ceil to achieve a correct rounding up or down. I will give a couple more examples of what i want to achieve, in the hope that somebody understands my requirements.

actual value 223.234, required value 223.2300, but rounded to 2 places, so 223.23

actual value 125.987, required value 125.9900, but rounded to 2 places, so 125.99

ok, hopefully my babbling will make sense to someone.

I just had a thought, is there a way of using floor & ceil in the same realtion, to achieve what i want, if so, how would you write that.

Best Regards

John

    Best answer by JonathanHodgson

    If you use floor(value+0.005, 2), this should always round correctly to the nearest value.

    For example:

    125.987 + 0.005 = 125.992

    floor(125.992, 2) will then give 125.99

    223.234 + 0.005 = 223.239

    floor(223.239, 2) still gives 223.23

    This is a useful trick in Excel, or in programming languages which don't have a true ROUND function.

    2 replies

    M
    MartinHanak24-Ruby III
    24-Ruby III
    March 1, 2011

    John,

    my suggestion is to test nonrelevant digits on 3rd, 4th, ... place after decimal point.

    Try following relations:

    ---

    if ((d8*100)-floor(d8*100))<0.5
    new_d8=floor(d8*100)/100
    else
    new_d8=(floor(d8*100)+1)/100
    endif
    ---

    -OR-

    ---

    if (d8-floor(d8,2))<0.005
    new_d8=floor(d8,2)
    else
    new_d8=floor(d8,2)+0.01
    endif

    ---

    Good luck

    Martin

    J
    John.Pryal1-VisitorAuthor
    1-Visitor
    March 1, 2011

    Thank you for the reply Martin,

    i will give it a go. Having said that, if there is an easier solution, i would appreciate it. I am really trying to avoid complex relations, as they prove to be unpopular within the company.

    Regards

    John

    J
    JonathanHodgson13-AquamarineAnswer
    13-Aquamarine
    March 8, 2011

    If you use floor(value+0.005, 2), this should always round correctly to the nearest value.

    For example:

    125.987 + 0.005 = 125.992

    floor(125.992, 2) will then give 125.99

    223.234 + 0.005 = 223.239

    floor(223.239, 2) still gives 223.23

    This is a useful trick in Excel, or in programming languages which don't have a true ROUND function.

    J
    John.Pryal1-VisitorAuthor
    1-Visitor
    March 8, 2011

    Thank you Jonathan,

    i altered my relations to suit, & now i am getting the values rounded how i want.

    Best Regards

    John

    Terms & ConditionsAccessibility statement