October 17, 2015

Dear All,

Does PQ have an equivalent of Excel's MROUND formula?

I need to round something to the nearest 0.5 and I cannot figure out how.

Thank you,

Blanka

July 16, 2010

Hi Blanka,

You can use Number.Round for this. e.g.

=Number.Round([value column], 1,RoundingMode.Up)

Or for rounding down:

=Number.Round([value column], 1, RoundingMode.Down)

More examples and rounding functions available at the link above.

Mynda

October 17, 2015

Hi Mynda,

Thank you for your idea.

I did check out the rounding functions that you had mentioned. However, I need to round to the nearest 0.5, so sometimes it would be RoundingMode.Up and others RoundingMode.Down.

I may need to figure out some kind of logic which would determine which method to use on a case by case basis. I just don't have any ideas yet.

Thank you again.

Blanka

July 16, 2010

Hi Blanka,

How about this Power Query formula:

= if

[Value] - Number.IntegerDivide([Value],1) < 0.25

then Number.RoundDown([Value],0)

else if

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

and

[Value] - Number.IntegerDivide([Value],1) < 0.75

then (Number.RoundDown([Value],0)+0.50)

else if

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

then Number.RoundUp([Value],0)

Mynda

Answers Post

October 17, 2015

Many thanks, Mynda.

This works like a charm.

I made a tiny modification; I added >=0.25 and >=0.75, instead of just >0.25 and >0.75 as this way it was rounding to 0.25 in some cases.

I entered:

= if

[Value] - Number.IntegerDivide([Value],1) < 0.25

then Number.RoundDown([Value],0)

else if

[Value] - Number.IntegerDivide([Value],1)** >=** 0.25

and

[Value] - Number.IntegerDivide([Value],1) < 0.75

then (Number.RoundDown([Value],0)+0.50)

else if

[Value] - Number.IntegerDivide([Value],1) **>=** 0.75

then Number.RoundUp([Value],0)

Thank you again, I appreciate your help.

Blanka

New Member

October 26, 2017

**Tip 2**: There are some another Round Example you can use also if need:

Example 1: Round to nearest million with one decimal point

(312789123 to 312.8)

=ROUND(A1/1000000,1)

This formula divides the number by million (1,000,000) and then round this to 1 decimal point.

**Example 2:** Get next even number

(42.1 to 44)

=EVEN(A1)

Gets you the next EVEN number (away from zero) 44.

**Example 3:** Get next odd number

(44.93 to 45)

=ODD(A1)

Gets you the next ODD number (away from zero) 45.

**Example 4:** Round to next 9 (i.e 19,29,39 etc.)

(23 to 29)

=ROUNDUP(A1,-1)-1

To do this, we just round up the number to next 10 and then subtract 1 from it.

Hope you liked it. Comment your take on the ROUNDUP Function in Excel.

Active Member

January 14, 2019

Hi Mynda,

I cannot apply your PQ Formula wrote on October 1, 2017 to my query. My problem basically the same, to rounding up to the nearest 0.0005, so i can lookup to the lookup table. In excel maybe I can use CEILING function to the nearest 0.0005.

Kindly need your formula to write in my query, I attached the excel file.

Thank you.

Chandra

November 8, 2013

Hi Chandra,

Number.Round has multiple rounding modes, have you tried them all? Here is a link to the resource, you have multiple examples on that page.

1 Guest(s)