# Round formula

#### RAJESH1960

##### Active Member
Hello Everyone, Wish you a very Happy and Colorful Diwali.
I have a data here where I have to enter the data in columns A:J and K:Q are auto calculated. I tried the round function to get the auto calculations to round to 2 decimal points but as you see some columns are displaying more than 2 digits. I need someone to help me correct my mistake.?
Book2
ABCDEFGHIJKLMNOPQR
1Gross2.52.566995A12A18A512185A12A18Around off
2939890204.29204.2973.7273.7234.2034.2040549.208085.17380.008171.601228.67380.00810984.0067376.422111.110000000000000-0.590000000
32640.0062.8662.862514.400.000.000.000.000.000000000000000-0.120000000
46885.00163.94163.946557.600.000.000.000.000.000000000000000-0.480000000
552080.001013.731013.73485.11485.1134.2034.2040549.208085.17380.000.000.000.000000000000000-0.450000000
69152.00435.820.000.000.008716.400.000.000000000000000-0.220000000
76290.00149.76149.765990.400.000.000.000.000.0000000000000000.080000000
84600.00219.050.000.000.004381.000.000.000000000000000-0.050000000
9
Sheet1
Cell Formulas
RangeFormula
K2:K8K2=ROUND(B2/2.5*100,2)
L2:L8L2=ROUND(D2/6*100,2)
M2:M8M2=ROUND(F2/9*100,2)
N2:N8N2=ROUND(H2/5*100,2)
O2:O8O2=ROUND(I2/12*100,2)
P2:P8P2=ROUND(J2/18*100,2)
Q2:Q8Q2=ROUND(A2-SUM(B2:P2),2)

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Joe4

I tried the round function to get the auto calculations to round to 2 decimal points but as you see some columns are displaying more than 2 digits.
Where?
I am not seeing any cells that show more than two decimal places in your example that you posted.

#### PeterDH

##### New Member
I am not sure, but should you not format these cells as number with 2 digits behind the point ? it seems that after 2 digits everything is zero.

#### RAJESH1960

##### Active Member
I am not sure, but should you not format these cells as number with 2 digits behind the point ? it seems that after 2 digits everything is zero.
columns P and Q

#### Joe4

columns P and Q
You have elected to show more than two decimal places, but all the values after the second decimal place are 0, so everything appears to be working as it should.

Note that the ROUND function does not control the number of decimal places it shows. It control the number of decimals it calculates to.
The number format applied to the cell controls how many decimal places are shown
.

If you show more than is calculated, it just shows 0 for everything after that, like shown in your example.

#### RAJESH1960

##### Active Member
You have elected to show more than two decimal places, but all the values after the second decimal place are 0, so everything appears to be working as it should.

Note that the ROUND function does not control the number of decimal places it shows. It control the number of decimals it calculates to.
The number format applied to the cell controls how many decimal places are shown
.

If you show more than is calculated, it just shows 0 for everything after that, like shown in your example.
I didn't get you Joe. I have given the round function to calculate the number to 2 decimal places right. Then why is it showing more than 2 in columns P and Q. Is there any other function to correct it.?

#### Joe4

ROUND only has to do with calculation, it has nothing to do with display.
Format of the cell determines how many decimal places to show.
You mustn't confuse calculation with display. They are totally separate things.

To change them to only show two decimal places, do the following:
1. Select columns P and Q
2. Right-click in any cell in those two columns
3. Select "Format cells"
4. Go the "Number" tab, and under Category choose "Number"
5. Under "Decimal places", pick 2
6. Click OK.

#### RAJESH1960

##### Active Member
ROUND only has to do with calculation, it has nothing to do with display.
Format of the cell determines how many decimal places to show.
You mustn't confuse calculation with display. They are totally separate things.

To change them to only show two decimal places, do the following:
1. Select columns P and Q
2. Right-click in any cell in those two columns
3. Select "Format cells"
4. Go the "Number" tab, and under Category choose "Number"
5. Under "Decimal places", pick 2
6. Click OK.
The columns with the formula will be protected and hidden and will be run with a macro, I will have to un-protect the sheet every time to do that. Is there any other solution rather then manually doing it.

#### Joe4

The columns with the formula will be protected and hidden and will be run with a macro, I will have to un-protect the sheet every time to do that. Is there any other solution rather then manually doing it.
If they will be hidden, what difference does it make how many decimal places it shows? If it is hidden, no one is going to see it.

For what its worth, it is really easy to add those "manual steps" to the VBA code.
Just turn on the Macro Recorder as your record yourself doing that manually. Then you will have the VBA code that you need to add to your existing procedure.

#### RAJESH1960

##### Active Member
If they will be hidden, what difference does it make how many decimal places it shows? If it is hidden, no one is going to see it.

For what its worth, it is really easy to add those "manual steps" to the VBA code.
Just turn on the Macro Recorder as your record yourself doing that manually. Then you will have the VBA code that you need to add to your existing procedure.
Yes. I can add those to the VBA code, But I was hoping to find if there was any other way to round the amount to exactly decimal places without writing that in the code. If I don't find any solution I think I will have to go with adding that to the code. Thanks for your suggestion.

Replies
11
Views
157
Replies
9
Views
199
Replies
6
Views
120
Replies
3
Views
171
Replies
4
Views
143

1,127,225
Messages
5,623,488
Members
415,973
Latest member
charlesbm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back