# Forcing a ROUND() Cell to be a Constant Number

1. ## 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:

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:

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]  Register To Reply

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

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

3. ## 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,...)  Register To Reply

4. ## Re: Forcing a ROUND() Cell to be a Constant Number Originally Posted by adyteo Why don't you just split the points as:
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!  Register To Reply

5. ## Re: Forcing a ROUND() Cell to be a Constant Number Originally Posted by RobertMika 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!  Register To Reply

6. ## 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")...   Register To Reply

7. ## Re: Forcing a ROUND() Cell to be a Constant Number Originally Posted by RobertMika 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  Register To Reply

8. ## 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%  Register To Reply

9. ## Re: Forcing a ROUND() Cell to be a Constant Number Originally Posted by adyteo 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.  Register To Reply

10. ## 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  Register To Reply

11. ## Re: Forcing a ROUND() Cell to be a Constant Number Originally Posted by Ace_XL 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!  Register To Reply

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