help on round up

[shj92922]

hi
I want help on round up as follow
e.g
33.25 must be 33.5 because it is greater than 0.25
33.13 must be 33 because it is less than 0.25
33.8 must be 34.0 because it is greater than 0.75
33.73 must be 33.5 because it is less than 0.75

[Pepe Le Mokko]

Perhaps try the MROUND function?

[davsth]

33.25 must be 33.5 because it is greater than 0.25 logic round up to the nearest 0.25 (even though already a multiple of 0.25)
33.13 must be 33 because it is less than 0.25 logic round down to the nearest 0.25
33.8 must be 34.0 because it is greater than 0.75 logic round up to the nearest 0.25 or round to nearest 0.25
33.73 must be 33.5 because it is less than 0.75 logic round down to the nearest 0.25 or round to nearest 0.25

These are a strange set of conditions (are you sure?)


State your rounding conditions in words, as there is a lot of guesswork here

[shj92922]

this calculation work for cubes report on our department
so I need a formula (one only) to solve my problem
sometimes the value is 33.25 or 33.13 or 33.8 or any number so I need to round it up as example shown

[davsth]

so 33.13 should be 33.25 not 33
and would 33.25 round up to 33.5 even though it is already a multiple
=CEILING(A1*4,1)/4


or if the 0.25 needs to be 0.5
=IF(MOD(A1*4,1)=0,0.25,0)+CEILING(A1*4,1)/4

[shj92922]

thanks man
the second formula worked but not for 33.13 (33.13 should be 33) according to engineering

[davsth]

I refer you to post 3, what are your rules they are inconsistent. Everything else rounds up, but it goers down. what other values would go down?

I imagine you could get any decimal values, what about .12 or 0.14 do they go up or down? without you explaining the rules you will not get an answer that works

=IF(MOD(A1*4,1)=0,0.25,0)+CEILING(A1*4,1)/4 +if(mod(a1,1)=0.13,-0.25,0) works but this can not be the correct answer but fits your sample.

[shj92922]

what he said to me that any fraction value less than 0.25 will round down to 0 (33.13=33)
fraction fall between 0.25 and 0.74 will round up to 0.5 (33.73 = 33.5) (33.25= 33.5)
fraction fall between 0.75 and 0.99 will round to next number (33.75= 34)

anyway he said to me he will read the standard again and will come back to me

sorry for my first post was unclear

[davsth]

perhaps =MROUND(A1,0.5)


Your rules are clearer now, less guesswork

[shj92922]

yea that's what happen when I call him to clear for me what he need
for me it is work
will share that to my Engineering if he said yes I add repution for you and mark as solved :D

[Pepe Le Mokko]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[shj92922]

I know that man

thanks every thing done add reputation and mark it solved