+ Reply to Thread
Results 1 to 11 of 11

Forcing a ROUND() Cell to be a Constant Number

  1. #1
    Forum Contributor
    Join Date
    06-08-2008
    Location
    USA
    MS-Off Ver
    Mac Excel 2016
    Posts
    103

    Forcing a ROUND() Cell to be a Constant Number

    This might end up being more of a math question than an Excel question -- but the way my spreadsheet's set up so far is that each employee should have a goal of, say, 100 for how many people they need to sign up. But they each have specific geographic breakdowns in their turf, some of which might be bigger or smaller than others, but at the end should all add up to 100 for each employee.

    It's not super hard to do since I just take a goal of 100, in this example, and multiply it by the percent of population of the area in their overall assignment and give them a goal for that area based on that. So if Philadelphia has 70% of their total population, Upper Darby has 20% of their total population, and Phoenixville has 10% of their total population -- the goals would come out like this:

    Philadelphia, Goal: 70
    Upper Darby, Goal: 20
    Phoenixville, Goal: 10
    Ryan, Total Goal: 100

    For some of the employees, that works out perfectly -- but based on the population sizes, the rounding sometimes gets a little off and it could end up like this:

    Philadelphia, Goal: 71
    Upper Darby, Goal: 19
    Phoenixville, Goal: 11
    Ryan, Total Goal: 101

    For most employees, it ends up being dead-on 100 -- but there are some whose totals are at 98 or 99 or 101, and I was wondering if there was a way to force it to shave a point off or add a point on somewhere, pending on how close the decimal was when it rounded to make sure that it always ends up exactly on 100. (I'm using 'Data >> Subtotals' to get what's being represented above as "Ryan, Total Goal.")

    Thanks for the help!

    [Edit: Here's a spreadsheet to better see what I'm talking about. Doesn't exactly match the example above, but it's close:
    SubtotalForcedConstantExample.xlsx]
    Last edited by rylock; 12-03-2013 at 11:22 AM. Reason: Edited to add example spreadsheet.

  2. #2
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Forcing a ROUND() Cell to be a Constant Number

    Why don't you just split the points as:
    Philadelphia, goal: 71
    Upper Darby, Goal: 19
    Phoenixville, Goal: 100-Philadelphia (71) - Upper Darby (19) = 11
    Such way you will always have the sum of 100

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Forcing a ROUND() Cell to be a Constant Number

    Why not to ad an IF statement?
    IF(AND(result is>98,result<101),100,...)
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  4. #4
    Forum Contributor
    Join Date
    06-08-2008
    Location
    USA
    MS-Off Ver
    Mac Excel 2016
    Posts
    103

    Re: Forcing a ROUND() Cell to be a Constant Number

    Quote Originally Posted by adyteo View Post
    Why don't you just split the points as:
    Philadelphia, goal: 71
    Upper Darby, Goal: 19
    Phoenixville, Goal: 100-Philadelphia (71) - Upper Darby (19) = 11
    Such way you will always have the sum of 100
    I'd love to do something like that -- but there's over 300 areas to split up for 25 different people, and they all have a formula that's a little more complicated than being able to type out the last area goal for each person as a simple subtraction formula:

    Please Login or Register  to view this content.
    In this case, G2 is looking up the goal name it its own tab and brining back the total number assigned that needs to be broken down for each person (in this example, 100). Then it's multiplying that by L4 which is the size of that area's population, then dividing that using a SUMIF() statement looking up-and-down Column B for how many times the employee's name populates (in this example, "Ryan"), then dividing L4 in that particular cell by the total population in his turf (Column L).

    So, ideally, there would be a way to tweak the aforementioned formula -- which works well -- to get the final subtotal to never be 99 or 101, but always 100.

    Thanks!
    Last edited by rylock; 12-03-2013 at 03:01 AM.

  5. #5
    Forum Contributor
    Join Date
    06-08-2008
    Location
    USA
    MS-Off Ver
    Mac Excel 2016
    Posts
    103

    Re: Forcing a ROUND() Cell to be a Constant Number

    Quote Originally Posted by RobertMika View Post
    Why not to ad an IF statement?
    IF(AND(result is>98,result<101),100,...)
    Thanks, Robert! I got a little more specific in my last post if you don't mind taking a look. An IF() statement would work well but the only formula that's ever in the 'Ryan Total' cell is the automatic subtotal formula. The only ones I have the ability to edit with whatever I want are the goals that are populating each specific area that's supposed to aggregate to 100. Here's an example of the first automatic formula that's in 'Ryan Total':

    Please Login or Register  to view this content.
    And it's obviously always =SUBTOTAL(9,...) but the "G3:G5" in this example is populated with different cells pending how many areas a person has (Eg. G6:G11, G12:G13, etc.). Point being, they're all different -- so I don't believe there's a way for me to add an IF() statement to a Subtotal formula that's being created using the Data >> Subtotal function. But if there is something I don't know about, that'd be great!

  6. #6
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Forcing a ROUND() Cell to be a Constant Number

    it woudl be good to see a part of your workbook (do now worry about the "convulsion")...

  7. #7
    Forum Contributor
    Join Date
    06-08-2008
    Location
    USA
    MS-Off Ver
    Mac Excel 2016
    Posts
    103

    Re: Forcing a ROUND() Cell to be a Constant Number

    Quote Originally Posted by RobertMika View Post
    it woudl be good to see a part of your workbook (do now worry about the "convulsion")...
    Yup! Sorry, I should've done it from the start. I just made a new quick example sheet [attached] that's only comprised of the parts I'm talking about. In the example, you can see that Ryan's totals are meant to add up to 100 -- but based on the way the cities break down, the subtotal only aggregates to 99 due to rounding. But Mary's subtotal works fine. Ideally, there would be a way for me to add something to my existing formula (which is now a little different than the one I posted above so that it would fit this new example) that would make the automatic subtotals always total 100:

    Please Login or Register  to view this content.
    Thanks!

    SubtotalForcedConstantExample.xlsx

  8. #8
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Forcing a ROUND() Cell to be a Constant Number

    have you tried to use MROUND in your formula?
    =MROUND(1.3, 0.2) Rounds 1.3 to a nearest multiple of 0.2 (1.4)

    You may be able to get closer to 100%

  9. #9
    Forum Contributor
    Join Date
    06-08-2008
    Location
    USA
    MS-Off Ver
    Mac Excel 2016
    Posts
    103

    Re: Forcing a ROUND() Cell to be a Constant Number

    Quote Originally Posted by adyteo View Post
    have you tried to use MROUND in your formula?
    =MROUND(1.3, 0.2) Rounds 1.3 to a nearest multiple of 0.2 (1.4)

    You may be able to get closer to 100%
    Thanks -- and correct me if I'm not thinking about this properly, but I can't use MROUND() in this case, because I still need people know what their goal is by location (Philadelphia, Upper Darby, etc.) -- and if I give people goals for Philadelphia that is 94.2 and Upper Darby that is 4.7, they're going to be understandably very confused as to how they sign up 0.2 additional people in Philadelphia and 0.7 people in Upper Darby.

  10. #10
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Forcing a ROUND() Cell to be a Constant Number

    One way of tackling this using adyteo's suggestion of having the last occurrence have the balancing number ..

    In D2 (o your sample sheet)

    =IF(COUNTIF($A$2:A2,A2)=COUNTIF(A:A,A2),Sheet2!$B$1-SUMIF($A1:$A$2,A2,$D1:$D$2),ROUND((VLOOKUP($D$1,Sheet2!$A$1:$B$1,2,FALSE)*Sheet1!C2)/SUMIF($A:$A,A2,$C:$C),0))

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  11. #11
    Forum Contributor
    Join Date
    06-08-2008
    Location
    USA
    MS-Off Ver
    Mac Excel 2016
    Posts
    103

    Re: Forcing a ROUND() Cell to be a Constant Number

    Quote Originally Posted by Ace_XL View Post
    One way of tackling this using adyteo's suggestion of having the last occurrence have the balancing number ..

    In D2 (o your sample sheet)

    =IF(COUNTIF($A$2:A2,A2)=COUNTIF(A:A,A2),Sheet2!$B$1-SUMIF($A1:$A$2,A2,$D1:$D$2),ROUND((VLOOKUP($D$1,Sheet2!$A$1:$B$1,2,FALSE)*Sheet1!C2)/SUMIF($A:$A,A2,$C:$C),0))

    Copy down
    Great, thanks! This seems to work and I will definitely use it if people on here think it's the best way. Ideally, the formula (I think) would round any of the numbers with the highest decimal point up to the next number regardless of whether it's the first, second, or third value (and especially because some people have up to eight locations, and others only have two) if the subtotal ends up being 99; similarly, it would take whichever number has the lowest decimal and round that down if the subtotal ends up being 101 -- just to get it as accurate as possible and not assume that the last value is always the spoiler.

    But if you guys think changing the final number is best, I'm good with that too -- as altering a goal by one isn't the biggest deal.

    Thanks again!

+ 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. Replies: 3
    Last Post: 03-08-2013, 12:51 PM
  2. [SOLVED] How to add a constant number to a cell
    By janierenee in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-06-2013, 04:46 PM
  3. Multiply range with constant and round results
    By ciprian in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2011, 04:58 AM
  4. [SOLVED] Copying and Pasting a cell with a constant number
    By DenDz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-31-2006, 03:00 PM
  5. [SOLVED] adding 0's to the left side of a constant number cell
    By faisal alfadl in forum Excel General
    Replies: 1
    Last Post: 11-25-2005, 01:10 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