+ Reply to Thread
Results 1 to 5 of 5

Help with 'selective' rounding

  1. #1
    Registered User
    Join Date
    02-05-2008
    Posts
    17

    Help with 'selective' rounding

    Hello,
    Hoping to get some help on a formula as I can't get the result I'm looking for.

    I am trying to calculate an adjusted years in service total. I have a employment start date and an end date. This part is pretty straight forward end - start / 365. I need it to be more precise, so I broke it down by months, also simple. Here is the kicker which I haven't found a solution to, any period of more than 6 months after the first year will count as a full year. I thought it would work out using the ceiling.math function with a significance of .5 It works great using that for years of service that equal X.5 and greater, rounds up to the next whole number. What I need it to do however, is if the total years in service <= X.00 - X.49 for it to leave the original number.

    In the attached, I'm trying to get column K using the value of column J.
    If the value in column J = X.00 to X.49, then K=value in column J
    If the value is X.50=X.99, then K=Ceiling.math(j3,0.5)

    I don't know how to make the above a reality
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,708

    Re: Help with 'selective' rounding

    A little clunky but this will do it for you...
    =IF(--MID(J3,SEARCH(".",J3),2)>=0.5,CEILING.MATH(J3,0.5,),--LEFT(J3,SEARCH(".",J3)-1))
    Last edited by Sam Capricci; 04-13-2022 at 10:11 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,502

    Re: Help with 'selective' rounding

    It appears like it should be as simple as testing for the fraction and choosing which function to apply. Something like:

    1) a MOD() function to get the fractional part of the value MOD(J,1), this will return a value between 0 and 1 that represents just the fractional part of the value.
    2) Test if that value is less than 0.5 (or greater than or equal to 0.5) =IF(MOD(...)<0.5,J,CEILING(J,1)) J is the reference to the appropriate cell in column J.

    Does that help, or did I misunderstand something?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    3,257

    Re: Help with 'selective' rounding

    Maybe try this:

    =IF(J3-ROUNDDOWN(J3,0)<0.5,J3,CEILING.MATH(J3,0.5))

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.97.2 for Mac MS 365
    Posts
    8,708

    Re: Help with 'selective' rounding

    I think this will work better for you...
    =IF(MOD(J3,1)<0.5,INT(J3),CEILING.MATH(J3,0.5,))
    I found the other one gave a value error at J36.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Banker's Round/Half to Even Rounding Double Rounding
    By CastorSunshine in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2019, 02:51 PM
  2. MROUND Rounding Issues (Rounding Down, Not Up)
    By roundandout in forum Outlook Formatting & Functions
    Replies: 4
    Last Post: 06-16-2015, 10:39 PM
  3. [SOLVED] Rounding problems using lookup instead of rounding functions
    By thnkfree in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-18-2014, 06:21 PM
  4. [SOLVED] Selective SUM
    By bryan444 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-11-2013, 01:24 PM
  5. Replies: 5
    Last Post: 12-11-2012, 05:04 AM
  6. VBA Help with Selective Sorting
    By drgogo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2009, 11:43 AM
  7. [SOLVED] Worksheet rounding vs VBA rounding
    By Simon Cleal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2005, 09:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1