Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



How To: Stop Rounding A Percentage

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Good evening,

I am trying to stop excel from rounding up when a cell is a percentage.

Ex..

B1 = 97.95%
B2 should equal 97%

How can I do this?

View Answers     

Similar Excel Tutorials

Round Numbers Up or Down in Excel
How to round a number up or down and also to a specified number of decimal places in Excel.  This will allow you to ...
Show Fewer Decimal Places Without Losing Precision in Excel
How to show a smaller number that looks better without sacrificing decimal places or precision in the actual number ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
Thermometer Chart in Excel
How to create a thermometer chart in Excel. This is what we want: Steps to Create a Thermometer Chart in Excel Mak ...

Helpful Excel Macros

Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Print Preview Display for Specific Worksheets in Excel
- This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.

Similar Topics







Hey guys,

I am in need of a help with plugging in the excess rounding to the largest percentage. This is done for tax calculation.

Due to the rounding issue, the remaining amount is sometimes off by a dollar or two, what I need to do is added that excess rounding to the largest percentage. I can get this done if I work based on cells, but that would then be redundent having two columns, is there a way I can get this done just from one column?

The way the spreadsheet is setup is that there is a total amount (highlighted in red) this amount gets distributed by each partner via there percentage. The partner with the largest % gets the remaining excess rounding. For this purpose I exaggerated the remaining amount, usually they are off by a dollar or two.

Thank is advance!


Uploaded with ImageShack.us


Excel is rounding dollar figures on my sheet to the nearest dollar, but I don't want it to do this. After looking at the formatting I don't really see why it is rounding, but I'm sure I'm missing something.

How do I get Excel to leave the cents in my cells and not round?

Thanks!

Hank


This is a really puzzling problem. Regardless of whether you are use to using VBA, Formula or generally adept using Excel, this difficulty in excel is a challenge.

Suppose in Column A, you have 100 cells with purely numeric data in each cell. At the bottom in A101, you have a total. Let us suppose they are large integers, and you wish to no know the percentage a1 is of the total, there are various methods of finding a solution.

If this percentage is displayed to two decimal places, the total of percentages should equal 100. If you wish this information displayed exactly to 2 decimal places there are equally various methods of formatting the data. If you then copy the percentages and past special and re total the percentages, they rarely return to 100, due to roundings.


I have tried many rounding formulas, that will give 2 decimal places and sum neatly to 100, there is normally manual changes required.

I have finally reach this formula to minimise the rounding errors:
Where the total is A620 and the percentage of cell A1 is calculated using the following:

MROUND(A1,($A$620*0.01/100)))/$A$620*100


The above is trying to round to multiples of 0.01% of the total (cell A620).

I have tried many many different formulas, but with a large amount of data there does not seem to be a solution.


Can anyone help?


I am doing come calculations and end up with 139.7 for example. If I format
the cell as a number with no decmial points it automatically rounds up to
140. Normally this is ok, but in this case I don't wnat it to round. Can I
turn the rounding feature off?



I would like to be able to shut off the rounding function in excel. For
example: the number $1,428.627 I would like to have show up as $1,428.62.
Any help would be appreciated. Thank you.



Hi all. Hoping this is a simple one for you wise ones out there.

In column B I want to round up a list of numbers in colummn A to the nearest 0.5, e.g. 3.2 becomes 3.5, 6 stays at 6, etc.

I've tried MROUND, but can't stop that rounding down when appropriate.

As ever, thanks in advance.


Hi - I am trying to create a spreadsheet to use as a check-and-balance to use along with my payroll system. Some of the formulas are not equaling my payroll system. It appears Excel is rounding the total to an even number. How do I stop it from rounding?

Help please!

PlanBVA


On financial worksheets I am getting fractional rounding errors. How can
you format a cell so that if =(A1*B1) is the formula the result in the cell
will be a value such as $50.45 and not $50.457 even though it is formatted
for 2 decimal places.






Hello,

In cells A1, A2 and A3, I have three values formatted in percentage format. In cell B1, I type in a formula to calculate the average of the three, and it automatically gives the result in percentage format. I want this as a general number so I change it manually. The problem is that everytime I change the cell reference in the formula of B1, Excel automatically reverts back to the percentage format.

How can I stop this from happening? Thanks.


I have a set of data that is meant to distribute a certain number of items to different groups.

I have 10 groups, some will get more than others depending on previous usage. The problem is that I need the percentages to be in whole numbers and the total percentage needs to be 100%. I tried rounding but it doesn't work. Here is an example from one item's line.

Group#,1,2,3,4,5,6,7,8,9,10
Dist %,.1,0,.04,0,0,0,0,0,.87,0

These are rounded and it comes out to be 101%.


I need to round interest rates to the next 1/8th of a percentage or nearest 1/8th of a percentage. For example, if the rate is 6.285%, it should go to 6.375% if I want it to go to the next 1/8th of a % and it should go to 6.25% if I want it to go to nearest 1/8th of a %. How can I write a formula for that. Everything we've tried fails.
Thanks,


Is there a way to stop xcel from rounding totals up when adding a column or multiply two cells? My totals never seem to add up to the exact number.


D

G 15-Mar 14-Mar 15-Mar 13-Mar 21-Mar 23-Mar 20-Mar 17-Mar 12-Mar 18-Mar 24-Mar 23-Mar 20-Mar 24-Mar 12-Mar 17-Mar 20-Mar 13-Mar 20-Mar 17-Mar 12-Mar 26-Mar 20-Mar 20-Mar 20-Mar 23-Mar 20-Mar 25-Mar 21-Mar 18-Mar 15-Mar 20-Mar 25-Mar 23-Mar



Column D & G are both Date columns.

I need a formula to calculate as follows:

If the date in column is equal to or before the date in column D, must be counted as 1 and so forth. If the date in column G is past the date in column D must be counted as '0'. Finally the result should be in percentage.

e.g Total 17 dates in column D.

Total dates equal to or before the date in column D are 9


In this case percentage would be (9 * 100 / 17) = 52.94 which should be rounded off to 53%

Thanks


I'm new to using excel for my Appliance repair business and not very computer savy, sorry for stupid questions. But when I try to put 62.50 in a cell (??correct term??) it automatically goes the 63 when I shift to next cell(box). I can't keep it from rounding off. Also, when I add numbers in a descending column I have to type EACH number. How to I get it to do this automatically? Example:
1788
1789
1790
1791


Good Morning

I have a workbook that the accountants use each month to supply our clients with thier monthly financials. One of the problems is rounding between Excel and MRI (Host System). I am using an array formula when totaling the various columns and this takes care of most rounding issues.

What I would like to do is have a macro that can be assigned to a command button that the account can click on when there is a rounding issue. The macro would ask the accountant which cell that needs to be adjusted (the cells are protected) and then another box would pop up asking for the amount (i.e. .01, -.01) then the macro would add this to the formula in that particular cell.

Is something like this possible?

Any help would be greatly appreciated.

Mark


I'm sure this has been asked before and I did use the search but can't find anything. Now that we have excel 2010, is smart rounding now available?

What I mean by smart rounding is, for example:

1.2479
2.1269

3.3748 Sum

but if you round and sum then you get

1.25
2.13

3.37 Sum

I hope there is a way around to adjust 2.13 down to 2.12 so that the sum is 3.37.

Thanks in advance.


Let say I have the value 13.49% in cell A1 and 27 in cell B1. In cell C1 I have the following formula: "=ROUNDUP((A1*0.01)*B1,0)"

If cell A1 is not formatted as a percentage the formula produces the required results. Which is the rounded up value, 4. Otherwise I returns a 1.

Additionally, If reformat cell C1 to a percentage with zero decimal place and use the formula "=(A1*0.01)*B1". It will return the desired results, however it will have a percentage sign after it, which I do not want.

Does anyone know how I would edit my formula/formatting and have cell A1 formatted as a percentage and still get the desired results?


I understand that Excel, unlike Access, will not do
Banker's Rounding. Ultimately, 1.05 under Banker's
Rounding will round to 1.0. Under traditional rounding,
it will round to 1.1.

I have tried subtracting .04 and then applying the
rounding procedure in Excel to acheive the same results.
However, numbers like 1.09, with the calculation will
yield a result of 1.05, which still rounds to 1.1. Is
there some other Mathematical means of achieving the
desired results in Excel.

Sincerely,
Josh



I need help rounding numbers up to .6 down, and from .6 and higher round up. The problem is rounding functions start rounding up at .5

Example he 405,128.53 I want rounded to 405,128.
405,128.61 needs rounded to 405,129.


I have an excel pie chart which calculates percent of total. When I set the
format to display one decimal point (ie. 65.8% and 34.2%) the numbers
displayed are accurate. However, when I change the format to display no
decimal points, the chart displays 65% and 34%. The 65.8% is not rounding up
to 66%. How do I correct the rounding on the chart?



Hi, is it possible to hold the resultant value of a formula after its first use? Currently I have the formula =IF(O$3='STOP GO'!$E$5,'STOP GO'!$J16,N) inside the cells in question. Both O3 and Stop go E5 are date cells. This means that only when the two date cells are the same can the original cell =Stop go J16. New data is put into Stop Go each week and Stop Go E5 is changed to the relevant date. Because the cell J16 in Stop Go changes every week I want a record of its history, hence my need for the value to stick once the IF formula has been proved true for the first time.
Below is an html of the cells in question.
I am using office 03 on windows XP.


******** ******************** ************************************************************************> Microsoft Excel - HALLERTAU 18 02 08 STOP GO.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout O4 P4 Q4 R4 O5 P5 Q5 R5 O6 P6 Q6 R6 O7 P7 Q7 R7 O8 P8 Q8 R8 =
O P Q R 1 FEBRUARY MARCH     2   2008     3 39503 39510 39517 39524 4 =IF(O$3='STOP GO'!$E$5,'STOP GO'!$J15,N) =IF(P$3='STOP GO'!$E$5,'STOP GO'!$J15,N) =IF(Q$3='STOP GO'!$E$5,'STOP GO'!$J15,N) =IF(R$3='STOP GO'!$E$5,'STOP GO'!$J15,N) 5 =IF(O$3='STOP GO'!$E$5,'STOP GO'!$J16,N) =IF(P$3='STOP GO'!$E$5,'STOP GO'!$J16,N) =IF(Q$3='STOP GO'!$E$5,'STOP GO'!$J16,N) =IF(R$3='STOP GO'!$E$5,'STOP GO'!$J16,N) 6 =IF(O$3='STOP GO'!$E$5,'STOP GO'!$J17,N) =IF(P$3='STOP GO'!$E$5,'STOP GO'!$J17,N) =IF(Q$3='STOP GO'!$E$5,'STOP GO'!$J17,N) =IF(R$3='STOP GO'!$E$5,'STOP GO'!$J17,N) 7 =IF(O$3='STOP GO'!$E$5,'STOP GO'!$J18,N) =IF(P$3='STOP GO'!$E$5,'STOP GO'!$J18,N) =IF(Q$3='STOP GO'!$E$5,'STOP GO'!$J18,N) =IF(R$3='STOP GO'!$E$5,'STOP GO'!$J18,N) 8 =IF(O$3='STOP GO'!$E$5,'STOP GO'!$J19,N) =IF(P$3='STOP GO'!$E$5,'STOP GO'!$J19,N) =IF(Q$3='STOP GO'!$E$5,'STOP GO'!$J19,N) =IF(R$3='STOP GO'!$E$5,'STOP GO'!$J19,N) COG  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Hello everyone,

I've done my due diligence on this forum learning about Excel Rounding. I learned that Excel uses arithmetic rounding (which I don't want) and VBA uses Banker's Rounding (which I want). Here is my problem:

I have no VBA skills (I'm learning though). Could someone write me code that would command my worksheet to use Banker's rounding?? If not the whole worksheet,at least the range J6:K25, where all my data calculations will be. Start from: Sub () End Sub or Fun() End Fun; I repeat, I have no VBA skills.

Thank you anyone that can help.


HI all, I have a vexing problem for your consideration. I work in a laboratory and, as such, need to use scientific rounding (which means rounding to the even number ex: 1.5 rounds to 2, 2.5 rounds to 2, 3.5 rounds to 4 and so on)

I've created a very simple custom function roundwhole, which uses the VBA round function which does the scientific rounding. So far so good.

The oddity is that when I run the roundwhole function on a pure number it works fine but when I run it on a calculation it reverts back to rounding up, not rounding to even.

Example: Roundwhole (265.5) returns 256 - perfect. But roundwhole ((8.38-3.25)/.02) returns 257, not good. And (8.38-3.25)/.02 = 265.5, the same as the first part of the example.

So, if anyone has an insight on why the same number returns a different number with the same calculation based on whether it is calculated or not I would be most appreciative!

Thanks, and be sure and let me know if I can give any further info!

Kurt


I am using the rounding function in excel and its rounding to the 10's, is there a way to round to the nearest 5?


Is it possible to change the size of the percentage sign that Excel inserts
in cells when the number in the cell is a percentage? If there are a lot of
percentages in a table, it is very difficult to read the numbers because the
percentage signs detract from the numbers. It would be good if there were a
way to get Excel to automatically insert the percentage sign in a smaller
font.