Rounding Numbers up in increments of 0.25

[OKgo]

My attempt at rounding in powerquery is failing me and my sanity. Any pointers to get to the desired results would be very much apprecaited.

Final Roundingup:

=if

[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) = 0

then [Final Cleaning]

else if

[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) > 0.75

then (Number.RoundUp([Final Cleaning],0)+1)

else if

[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) > 0.25

then Number.RoundUp([Final Cleaning],0)+0.50

else if

[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) >= 0.01

then Number.RoundUp([Final Cleaning],0)+0.25

else null

[lbeneyze]

Good afternoon OkGo,

Instead of the function "Number.IntegerDivide", you may be looked at "Value.Divide" which deal beter with decimal numbers.

It is still longer than the solution proposed by artemus 😅.

I've written a generic custom function for that rounding.

/*
Return nearest rounded number by the provided interval
Example 1: number 2.15 with interval 0.25 will be rounded to 2
Example 2: number 2 with interval 0.25 will be rounded to 2
Example 3: number 2.35 with interval 0.25 will be rounded to 2.5

Parameter
    inputNumber: number that needs to be rounded. The number can be decimal
    inputInterval: interval to be taken account in the rounding. The number can be decimal

homepage: <>
*/

let
    RoundToNearest = (inputNumber as number, inputInterval as number) as number =>
        let
            
            //round down inputnumber
            NumberRoundDown = Number.RoundDown(inputNumber, 0),

            //get decimal from inputNumber
            NumberDecimals = inputNumber - NumberRoundDown,

            //
            NumberRest = Value.Divide(NumberDecimals, inputInterval),

            //
            NumberRestRoundDown = Number.RoundDown(NumberRest, 0),

            //
            NumberRounded = 
            if 
               NumberRest = NumberRestRoundDown
            then 
                inputNumber
            else 
                if 
                    NumberRest - NumberRestRoundDown <= 0.5
                then 
                    NumberRoundDown + (NumberRestRoundDown * inputInterval)
                else 
                    NumberRoundDown + ((NumberRestRoundDown+1) * inputInterval)
        in
            NumberRounded
in
    RoundToNearest

If you like my solution please give it a kudo. 👍

Kind regards,

Lohic Beneyzet

[arpost]

Greetings, @lbeneyze! I tried to use your custom solution above but found the results aren't as expected.

Example: I want to round numbers to the nearest 0.25 (e.g., 2.35 becomes 2.5). Using your solution above, when the inputNumber is 2.35 and the inputInterval is 0.25, the result is 2.25.

In example #3 in your M code above, however, it shows this would be 2.5, which is what's desired.

Example 3: number 2.35 with interval 0.25 will be rounded to 2.5

Is there something I need to tweak to get it to round as in example #3?

[artemus]

The example is a mistake 2.35 is closer to 2.25 (0.10 off) than 2.5 (0.15 off).

Here is a compact formula:

(inputNumber as number, inputInterval as number) as number =>
        Number.RoundDown(inputNumber / inputInterval + inputInterval / 2) * inputInterval

[OKgo]

The post was clearly roundup. 

I asked bing.com/new:
Number.RoundUp([Final Cleaning] * 4) / 4

I need this power query helper colum to round to the nearest increment of 0.25 not round up. Can you help

and it returned

if [Value] - Number.IntegerDivide ([Value],1) < 0.25 then Number.RoundDown ([Value],0) else if [Value] - Number.IntegerDivide ([Value],1) > 0.25

[OKgo]

@lbeneyzethere is a lot of general logic can learn from that post. Thank you for taking the time. 

[artemus · Accepted solution]

Number.RoundUp([Final Cleaning] * 4) / 4

[OKgo]

Spoiler

Thank you so much @artemus - that is so wicked smart and it works!

The second after I posted it dawned on me what I was doing wrong with my janky crazy approach. A version that works below for historical refernce 🙂

if
[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) = 0
then [Final Cleaning]
else if
[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) > 0.75
then (Number.RoundUp([Final Cleaning],0)-0.25)
else if
[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) > 0.25
then Number.RoundUp([Final Cleaning],0)-0.5
else if
[Final Cleaning] - Number.IntegerDivide([Final Cleaning],1) >= 0.01
then Number.RoundUp([Final Cleaning],0)-0.75
else null