I have been using the ROUNDUP / ROUNDDOWN functions to get whole numbers, but in some cases, I need to round up to the nearest 1/4 number. How would I go about creating a function to check 24.118, and then tell the software to round it up to 24.25? Thanks kindly.
As ‘Math’ doesn’t round anything I suppose you are talking of spreadsheet formulae. Please add the tag ‘Calc’ to your question.
Let’s talk of ROUNDing first. Let’s in addition call the number you want to get integer multiples of (1/4 in your example) the MODULUS, the number you want to be rounded the ARGUMENT. What you want to get is simply named the RESULT.
Then we may state: RESULT = ROUND(ARGUMENT / MODULUS) * MODULUS
ROUND is the Calc standard function. You may use ROUNDUP and ROUNDDOWN in the same way.
For even more sophisticated rounding (neither ‘nearest’ nor ‘up’ nor ‘down’) you will have to use the QUOTIENT function.
Thanks. I have been using roundup and rounddown, and I found another one called mround, but none of those have worked quite well enough. I will check out the quotient function.
I think can work rounding up with ROUND((Argument+Modulus/2-1) / Modulus) * Modulus or =MROUND(Argument+Modulus/2-1;Modulus), adding (Modulus/2-1) we get to round up.
@mariosv: imho your proposal fails for fractional MODULUS,
=ROUND((2,0001+0,25/2-1) / 0,25) * 0,25 → 1,25
be aware that for CEILING() ‘up’ is the direction from minus infinite to plus infinite for negative numbers rather than calculating ‘up’ to higher absolute amount,
and that there is a 3 parameter necc. for negative values which works a little odd on a first glance, =CEILING(-2,0001;0,25) → Err:502,
=CEILING(-2,0001;0,25;0) → Err:502,
=CEILING(-2,0001;0,25;1) → Err:502,
=CEILING(-2,0001;-0,25) → -2,
=CEILING(-2,0001;-0,25;0) → -2,
=CEILING(-2,0001;-0,25;1) → -2,25,
CEILING.MATH() and CEILING.PRECISE() work differently,
@newbie-02: Rounding seems to be an obsession of yours now. - or is it an addiction?
I just read again the specification for CEILING(), and decided to stick to my decision not to use functions needing a complicated specification of the kind. I would trust in INT() and base different ways of rounding on it if needed. If I am on error with my formula then, I will know whom to blame for - and how to fix it. Using a function probably implemented in an incorrect way due to its complicated specification, resulting errors would hit me out of the dark.