Rounding percentage's of the total

[jesterhs]

ok, I can't seem to figure this out. I have a total, lets say 29. I have
to take 85%, then 15%, and finally 5% of that number (23.2, 4.35, 1.45).
If you add them back up in a 2 decimal format, no problems. But what if
I want to show the results without the decimals or rounded. 23, 4, and
1 only adds up to 28.

Is there a way to show these as whole numbers, possibly using a
function, and get them to add back to the total. It doesn't seem
mathematically possible, but I am hoping that there is some way.

Thanks,
Howard

 

[Guest]

I presume you mean 80% not 85%
Just format the values you calculate to no decimal places. They will then be
displayed as 23, 4, and 1 but will still add up to 29 when you sum the
results since Excel 'remembers' the lost decimals, it just doesn't display
them.

 

[MrShorty]

1) I suspect you meant 80%, since 23.2=.80*29. .85*29=26.45. It isn't
the problem in this case, but make sure your percentages add up to
100%.

2) This is the hazard you run into when rounding, especially when
rounding intermediate calculations. If you try it with 30, you get 31.
Is there some reason you must round the numbers? Barring other
constraints, I would suggest formatting the cells to display 0 decimal
places. When you use a number format, the underlying cell's value
isn't changed. In other words, 23.2 displays as 23, but the cell's
value is 23.2. Then when you add up the three cells, you get 29
instead of 28. This all assumes that the cell calculation option
"precision as displayed" is not checked.

 

[Ken Wright]

Use a function to round all bar one of your values to whatever degree you
need, and then have the last value be the total minus the summation of the
others.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[jesterhs]

Yeah. I meant 80%. The reason that I have to show whole numbers is that
these #'s are loans. Can't really have 23.2 loans.

I should have mentioned this before, but the total line is based on a
separate calculation where a % of the total possible volume is taken to
get the total # of loans. Then from that total we take a % for each of
the 3 loan categories (80, 15, 5). So the Total of 29 is not a sum of
the 3 categories, but rather the 3 categories are populated by the sum
* the % of that specific category.



Jul - Aug - Sep - Oct - *Nov* - Dec - Annual

Conv 0 - 6 - 18 - 18 - *23* - 18 - 83
ALT A 0 - 1 - 3 - 3 - *4* - 3 - 15
Non 0 - 0 - 1 - 1 - *1* - 1 - 5

Total 0 - 7 - 22 - 22 - *29* - 22 - 103

 

[jesterhs]

Thank you all for your responses. Much appreciated