Log In
Register
Home
October 17, 2015
Offline
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
See Answer
July 16, 2010
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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.
July 16, 2010
Offline
Hi Darshan,
Thanks for sharing, but this question is about rounding in Power Query using M functions. Those are Excel round functions.
Mynda
Active Member
January 14, 2019
Offline
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
Online
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)
