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

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

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)