Round formula

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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)
 

Some videos you may like

Excel Facts

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 17, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2020
Messages
449
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top