Dividing a total using % but need whole number answers

[dhunt]

Hi

I have many totals that I need to divide between 44 variables, each variable has its own % needed of the total. So the total is being divided by % rather than a number. However I need excell to give me the results as whole number with no decimal places as they are units (for example you can't get 1.5 people it has to be 1 or 2) For example see below:

1.37 3.05 6.09 13.04 10.46 19.34 7.71 14.15 2.67 4.91 0.46 0.75 5.70 3.99 0.06 0.18 1.03 2.58 0.04 0.34 0.89 1.59 0.46 0.95 0.69 1.99 0.36 0.60 0.09 0.29 0.04 0.04 0.02 0.04 0.00 0.06 0.92 0.96 0.21 0.05 0.72 1.08 0.00 0.00

I can remove the decimal places which produces this:

1 3 6 13 10 19 8 14 3 5 0 1 6 4 0 0 1 3 0 0 1 2 0 1 1 2 0 1 0 0 0 0 0 0 0 0 1 1 0 0 1 1 0 0

However the total was 110 so when divided there needs to be 110 units however there is 109 due to the next biggest unit falling below 0.5.

Is there a way I can tell excell to round to a whole number so that all of the units are distributed as whole numbers?

Let me know if this doesn't make sense.

[jeffreybrown]

Have you looked at the round functions within Excel?

http://www.excelfunctions.net/Excel-Math-Functions.html

[MrShorty]

However the total was 110 so when divided there needs to be 110 units however there is 109 due to the next biggest unit falling below 0.5.

Anytime you round like this, you are going to have this kind of problem. Either your percentages won't add up to 100%, or your parts don't add up to the whole. It would appear from your post that it is more important to you that the parts add up to the whole than that the percentages add up to exactly 100%. The usual suggestion I see is to calculate all but 1 of the parts using the percentages. The final part is calculated as total-sum(other parts). You can minimize the error this introduces by using the largest portion for the subtraction.

[K m]

Suggest you manually promote one of the numbers ending in .46 to the next higher number

You could write a macro to do it but you are asking Excel to do something wrong mathematically. For example =Round (10.46,0) does not equal 11 and you would have to choose one of the numbers and promote it if you want it to work

[dhunt]

The round function doesn't work as it either round them all up or all down where I only want the top so many rounded up.

I can't not use % as that informs me how much of the whole each section needs. For example if I have 208 46 and 8. AB constantlly needs 46% BC always needs 16% and CD will always need 29% and DE constantly needs 9%.

Looks like it will have to be manually as I can't seem to find anything that I can apply to the whole data. Would work if I was only doing one line of data however there are 208 like the one I have shown in the first example.

[jeffreybrown]

How about you put together a small sample workbook with a before and after towards your requirements.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[dhunt]

I have attached an example of what I want to happen.

[jeffreybrown]

First, you want the total of Example A to be 4 which if you sum up C:G the sum is 4.

You show 0 for a couple of cells within that range, but how do you come up with 0?

C1 = 0.17 but you show as 0. Maybe some more explanation is required.

Please Login or Register  to view this content.