Email:      Pass:    Pass?

Subscribe for Free Excel tips & more!
E-mail:
Advertisements

# How To: Stop Rounding A Percentage

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?

## 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 ...
Quickly Create a Huge List of Numbers in Excel
Quickly create a large list of numbers in Excel using the Fill Command.  This will save you time and allow you to ...

## 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

Good Evening,

I have a spreadsheet that contains all months (in columns) August 2007 to December 2013. Every month has certain percentage, in some cases it is the same percentage through out the year and in some cases the percentage differ from month to month in the same year.

What I am trying to do is the following:

1) Identify in which month the percentage changes - (accomplished)
2) The month in which the percentage changes, I want to apply 1% on a certain dollar amount starting from the month the percentage changes and going all the way back to the beginning of the year.

So for example if the percentage changes in Aug 2007 I want to apply 1% starting from Aug 2007 all the way down to Jan 2007.

I created certain triggers that will allow me to accomplish the task, however I am unable to stop calculation going beyond the lowest month in the year.

I greatly appreciate any and all help.

Axim 5

Excel 2007

This is rahther unusual, here goes. I have 5 columns -
1. Price of Product
2. Percentage, which is random (between 75% and 95%)
3. Percentage total
4. Total price, adding percentage total to column 1
5. this is the one I can't figure out. I have it Rounding column 4 up and down fine, but need it to round (or calculate somehow) so that every rounded price ends up ending with 95 - example: 2025 should automatically be changed to 2095, etc.
Not even sure if this is possible - spent two days searching for the answer. If anyone out there knows how this could be done I would be very grateful.

Thanks

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

good morning,
I went through some of the back threads to see if that would solve my problem, but I couldn't find one specifically (or I just didn't look hard enough). anyway.

I am looking to round some \$ values. I have a formula that is calculating a percentage of a previous cost but some of the values are coming back as odd change (.13, .01, etc.). is there a general formula that would make these values round more appropriately? or do I need to set up the rounding formula specific to each cell based on what the original value is?

for example:
\$42.75 - OK as is
\$26.13 - should be \$26.15
\$14.01 - should be \$14.00
etc.

my original formula is this:
=(C16*(100%-\$O15))

with row C corresponding to the old prices and column O corresponding to the percentage off

thanks in advance for the help!

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.

hey just a quick one does anyone know how to turn the round function off or is there a round formula i can use to stop it rounding for me.

thanks

mark

Hi gang,

I have a spreadsheet, and it basically consists of 2 columns, one where there are titles, and one which has one of three values: Passed, Failed and N/A (it's a summary of test results).

I also have a percentage calculator, which calculates how much is left blank, by using the following formula:

Code:

```="Percentage Done = " & (((COUNTA(E2:E99))/((COUNTBLANK(E2:E99)) + (COUNTA(E2:E99))))*100) & "%"
```

(E2 to E99 is the column with the 3 outcomes).

This returns a value of "Percentage Done = 6.12244897959184%" when 6 out of the 92 columns are filled in.

What I want to know, is how to get that to say "Percentage Done = 6.12%" (i.e.) cut down the significant figures down to 3.

Preferably, I'd rather have the whole rounding bit in this one cell's formula.

Cheers,

RayJay.

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?

Hello all,

I have a very big data That consists of four columns : the first one is the time, the second one is the instructed word ( either GO or STOP ) , the third one is the instructed aim ( five numbers = 0 ( only for STOP ) 10 20 30 40 50 ( for GO ) ) and the last one is the achieved result. The word GO is always constant across the data ( i.e, it is always repeated 60 times every approx 0.05 seconds and it lasts for 3 seconds ). The word Stop is not however. They are alternative. So GO STOP GO STOP GO STOP GO STOP GO STOP. The Word GO in my data is repeated 4500 times. While the word Stop 5500. So the total RAW data is 10000. The problem is that I am facing is that I am trying to extract the achieved results or the fourth column with its corresponding time. If u take these data and plot the third column and the fifth one you will see a shift in time that varies across data. So What I am trying to do is to find a function or to extract the data in way that just gives me the achieved data ( the begging of it to the end ). So as an example this is the output :

0 GO 40 0
0.053 GO 40 0
0.103 GO 40 0
0.153 GO 40 0
0.204 GO 40 0
0.255 GO 40 0
0.306 GO 40 0
0.356 GO 40 0
0.406 GO 40 0
0.459 GO 40 0
0.51 G O 40 0
0.56 GO 40 0
0.611 GO 40 0
0.661 GO 40 0
0.711 GO 40 0
0.761 GO 40 0
0.811 GO 40 0
0.868 GO 40 0
0.918 GO 40 0
0.968 GO 40 0
1.019 GO 40 0
1.07 GO 40 0
1.12 GO 40 1
1.172 GO 40 2
1.222 GO 40 6
1.272 GO 40 8
1.322 GO 40 13
1.372 GO 40 18
1.427 GO 40 20
1.478 GO 40 24
1.528 GO 40 27
1.578 GO 40 28
1.628 GO 40 31
1.678 GO 40 32
1.729 GO 40 33
1.779 GO 40 35
1.829 GO 40 36
1.879 GO 40 37
1.929 GO 40 37
1.979 GO 40 38
2.03 GO 40 38
2.083 GO 40 39
2.134 GO 40 39
2.184 GO 40 39
2.236 GO 40 39
2.286 GO 40 39
2.336 GO 40 39
2.386 GO 40 39
2.436 GO 40 39
2.492 GO 40 39
2.542 GO 40 39
2.592 GO 40 39
2.643 GO 40 39
2.694 GO 40 38
2.744 GO 40 38
2.803 GO 40 38
2.854 GO 40 38
2.904 GO 40 37
2.955 GO 40 37
3.006 GO 40 37
3.056 STOP . 37
3.106 STOP . 37
3.156 STOP . 37
3.206 STOP . 37
3.256 STOP . 37
3.307 STOP . 37
3.357 STOP . 37
3.407 STOP . 37
3.459 STOP . 34
3.509 STOP . 32
3.559 STOP . 24
3.611 STOP . 14
3.661 STOP . 10
3.711 STOP . 3
3.761 STOP . 0
3.812 STOP . 0
3.867 STOP . 0
3.917 STOP . 1
3.967 STOP . 1
4.019 STOP . 1
4.069 STOP . 1
4.119 STOP . 2
4.171 STOP . 2
4.221 STOP . 2
4.271 STOP . 1
4.321 STOP . 1
4.371 STOP . 1
4.427 STOP . 1
4.477 STOP . 1
4.527 STOP . 1
4.579 STOP . 1
4.629 STOP . 1
4.679 STOP . 1
4.729 STOP . 1
4.779 STOP . 1
4.83 STOP . 1
4.88 STOP . 2
4.93 STOP . 1
4.98 STOP . 1
5.03 STOP . 1

What I want is to return this data :

1.172 GO 40 2
1.222 GO 40 6
1.272 GO 40 8
1.322 GO 40 13
1.372 GO 40 18
1.427 GO 40 20
1.478 GO 40 24
1.528 GO 40 27
1.578 GO 40 28
1.628 GO 40 31
1.678 GO 40 32
1.729 GO 40 33
1.779 GO 40 35
1.829 GO 40 36
1.879 GO 40 37
1.929 GO 40 37
1.979 GO 40 38
2.03 GO 40 38
2.083 GO 40 39
2.134 GO 40 39
2.184 GO 40 39
2.236 GO 40 39
2.286 GO 40 39
2.336 GO 40 39
2.386 GO 40 39
2.436 GO 40 39
2.492 GO 40 39
2.542 GO 40 39
2.592 GO 40 39
2.643 GO 40 39
2.694 GO 40 38
2.744 GO 40 38
2.803 GO 40 38
2.854 GO 40 38
2.904 GO 40 37
2.955 GO 40 37
3.006 GO 40 37
3.056 STOP . 37
3.106 STOP . 37
3.156 STOP . 37
3.206 STOP . 37
3.256 STOP . 37
3.307 STOP . 37
3.357 STOP . 37
3.407 STOP . 37
3.459 STOP . 34
3.509 STOP . 32
3.559 STOP . 24
3.611 STOP . 14
3.661 STOP . 10
3.711 STOP . 3

I hope I get an answer that helps me doing this

Thanks

AS

realy example excell forum.xlsx

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.