Results 1 to 4 of 4

Thread: nearest 5
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2015
    Location
    Egypt
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default nearest 5

    Hello,
    I am trying to define some range of numbers by the nearest down 5 (i.e. 7 to 5 and 43 to 40) is there any easy way to do it by PQ or
    PP ?
    Thanks.

  2. #2
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: nearest 5

    The standard approach I use is round(number/target,0)*target. So, in Excel use ROUND(x/5,0)*5. In PowerQuery, use Number.Round(x/5,0,roundingmode.awayfromzero) -- with the appropriate capitalization.
    Quote Originally Posted by ahmedismailfourtex View Post
    Hello,
    I am trying to define some range of numbers by the nearest down 5 (i.e. 7 to 5 and 43 to 40) is there any easy way to do it by PQ or
    PP ?
    Thanks.

  3. #3
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: nearest 5

    The question is a bit confusing as you don't want to round to the nearest, but you want to round down to a multiple of 5.
    That would be: 5*Number.RoundDown(x/5,0)
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

  4. #4
    Board Regular
    Join Date
    Apr 2015
    Location
    Egypt
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: nearest 5

    Quote Originally Posted by MarcelBeug View Post
    The question is a bit confusing as you don't want to round to the nearest, but you want to round down to a multiple of 5.
    That would be: 5*Number.RoundDown(x/5,0)
    that's absolutely right

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •