+ Reply to Thread
Results 1 to 4 of 4

Custom formulas that will round up when a condition is met or round down

  1. #1
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

    Custom formulas that will round up when a condition is met or round down

    The client that I am working for wants me to do some custom formulas or write vba code that will round some items.

    We have 3 formulas that produce the following numbers.

    50.77%
    24.62%
    24.62%

    Together they equal 100%. The customer does not want to show the decimal places. If you take the decimal places out you get
    51%, 25%, 25% which equal 101%. They want me to do some type of formula that will round up or down. For example... it would look at the largest decimal place and round that number up... if it did not equal 100%, it would look at the second number with the largest decimal and round it up, so on so forth, until it is the whole numbers equal 100%

    Below is the example they gave me on what they wanted me to do. I have never done any type of math functions like this in excel and do not know where to even start.

    We have 3 numbers that there sum is 100
    X1,X2,X3
    The number without the decimal(down round the numbers)
    Y1,Y2,Y3
    Summarize only the digits of the 3 numbers
    sum
    While sum<100
    {

    Increase by one the number(y1,y2,y2) that is decimal is the biggest (x1,x2,x3) and round down is X number
    }
    Example
    X1 =33.94 x2=32.98 x3= 33.06
    Round it down
    Y1=33 y2=32 y3 =33
    Sum of y is 98
    Increase by 1 the y that is x decimal is the biggest
    In this example X2 decimal is the biggest
    So Y2=Y2+1 and X2=RoundDown(X2)
    Now
    X1 =33.94 x2=32.0 x3= 33.06
    Y1=33 y2=33 y3 =33
    The sum of y now is 99 so we need to continue
    Increase by 1 the y that is x decimal is the biggest
    In this example X1 decimal is the biggest
    Now
    X1 =33.0 x2=32.0 x3= 33.06
    Y1=34 y2=33 y3 =33
    The sum of y now is 100 so we stop and present to the user: Y1=34 y2=33 y3 =33

    cinstanl

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,913

    Re: Custom formulas that will round up when a condition is met or round down

    Try this ...

    =INT(X1)+(MOD(X1,1)>=LARGE(MOD($X$1:$X$3,1),ROUND(SUM(MOD($X$1:$X$3,1)),0)))

    Enter with Ctrl+Shift+Enter.

    Copy down.
    Last edited by Phuocam; 06-20-2016 at 10:24 AM.

  3. #3
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

    Re: Custom formulas that will round up when a condition is met or round down

    Thank you for the reply. I tried the formula it is not doing what I need it to do. I have attached the spreadsheet that I placed it on, so that you could see it.

    Thanks
    cinstanl
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

    Re: Custom formulas that will round up when a condition is met or round down

    I no longer need assistance on this. Someone suggested that I use a helper column and I did. Worked out perfectly. I attached a screen print, in case anyone came across this and needed assistance.
    Attached Files Attached Files

+ 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. Round the custom range of row
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2015, 06:44 AM
  2. Replies: 2
    Last Post: 12-06-2014, 01:58 PM
  3. [SOLVED] How to round to nearest .01 and round down if thousands place is .005
    By dredre609 in forum Excel General
    Replies: 5
    Last Post: 09-29-2014, 11:47 AM
  4. Round with given condition
    By pnegi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-23-2013, 01:03 AM
  5. Custom Round Up Formula
    By John Bates in forum Excel General
    Replies: 4
    Last Post: 04-25-2011, 05:40 PM
  6. Custom round up sumation
    By Macee in forum Excel General
    Replies: 5
    Last Post: 01-05-2011, 06:30 AM
  7. [SOLVED] How do I ROUND() round off decimals of a column dataset?
    By H&oslash;jrup in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2005, 07:06 AM

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