After rounding/formatting to whole numbers...

[dsmart2]

I run into this problem quite a bit and haven't been able to find an easy
solution. I have a series of numbers that represent a percentage of the
total, ie 15.8, 67.7 and 16.5 which adds up to 100% and is fine. I need these
to be whole numbers though so after rounding or formatting (I've done both) I
end up with 16, 68 and 17 which adds up to 101 because of the rounding. I
have over 500 lines and I need to flag the ones that add to either 99 or 101
so that I can go in and manually adjust them (unless you can think of some
formula that will force them to 100) but I can't even begin to think of how I
can get it to use this whole number in the formula NOT the original 15.8
number.

 

[John Michl]

I'd assume that when you adjust the numbers you are probably adding or
subtracting 1 from one of the numbers in the list. If that is the
case, why not put a formula in the last cell that calculates the
adjusted number. If the percentages are in cells A1 through A50 you
could change A50 to =1-sum(A1:A49) to force them to add.

- John