Results 1 to 11 of 11

Forcing a ROUND() Cell to be a Constant Number

Threaded View

  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.

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. 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. 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