Register
Log In
Unanswered
Home
Excel Analysis Toolpak
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
Excel Analysis Toolpak
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
Excel Analysis Toolpak
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.
Most Users Ever Online: 57
Currently Online:
13 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Mohamed Rehan Memon
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
