98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » excel consulting - How do I stop the rounding up/down
excel consulting - How do I stop the rounding up/down
Resolved · Low Priority · Version Standard
Dawn has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Access Introduction course
Access Intermediate course
How do I stop the rounding up/down
Hello I have a small problem. I have to calculate signed percentages of business written. As you can see from the attachment I have a column (Written Line) which is the business percentage written per person, as you can see this column adds up to over 100%. I therefore have to sign these percentages down to 100% (ie signed line column). To do this we calculate the 100% gross divided by the overall total 175% and times by the written line amount for the individual person to get the signed line amount. I have set this to three decimal places. The overall total for this column shows 100% but if you were to actually add these amounts up on a calculator they total 100.001.
I think that because although I am showing 3 decimal places, somewhere in the background the amount of decimal places is continuous. Can you advise what I need to do to get this to calculate only three decimal places
Many thanks
Dawn
WRITTEN
LINE:
SIGNED LINE: SYND PSYD
25.00 14.286 570 ATR
25.00 14.286 727 SAM
30.00 17.143 9272 KLN
25.00 14.286 1200 BRT
16.20 9.257 2623 AFB
3.80 2.170 623 AFB
25.00 14.286 1206 GER
25.00 14.286 780 ADV
TOTAL 175.00 100.00
RE: How do I stop the rounding up/down
Hi Dawn
I recreated your data, and applied the formulas you wrote about, but do not seem to get the same error you are taking about. The only difference can see in our data is 2.170 vs 2171 in row 6.
WL: SL:
25.0 14.286
25.0 14.286
30.0 17.143
25.0 14.286
16.2 9.257
3.8 2.171
25.0 14.286
25.0 14.286
175.0 100.000
175 100
Did you copy the formula or rewrite it for all your calulations?
RE: How do I stop the rounding up/down
I tried to attach the file so you could see how I had calculated but this would not attached. The way I done the calculations for the signed lines we as follows:
=sum(100)/the total (175.00 cell)*cell C3 (which is the signed line cell) I then used the fill to do the same to the remaining cells.
Should I be using a different function key instead of the SUM
Dawn
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Sorting data stored in rowsPrimarily Excel is set up to sort data that is stored in columns rather than rows. |