Round formula

[RAJESH1960]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Gross	2.5	2.5	6	6	9	9	5A	12A	18A	5	12	18	5A	12A	18A	round off
2	939890	204.29	204.29	73.72	73.72	34.20	34.20	40549.20	8085.17	380.00	8171.60	1228.67	380.00	810984.00	67376.42	2111.110000000000000	-0.590000000
3	2640.00	62.86	62.86								2514.40	0.00	0.00	0.00	0.00	0.000000000000000	-0.120000000
4	6885.00	163.94	163.94								6557.60	0.00	0.00	0.00	0.00	0.000000000000000	-0.480000000
5	52080.00	1013.73	1013.73	485.11	485.11	34.20	34.20				40549.20	8085.17	380.00	0.00	0.00	0.000000000000000	-0.450000000
6	9152.00							435.82			0.00	0.00	0.00	8716.40	0.00	0.000000000000000	-0.220000000
7	6290.00	149.76	149.76								5990.40	0.00	0.00	0.00	0.00	0.000000000000000	0.080000000
8	4600.00							219.05			0.00	0.00	0.00	4381.00	0.00	0.000000000000000	-0.050000000
9
Sheet1

Cell Formulas
Range	Formula
K2:K8	K2	=ROUND(B2/2.5*100,2)
L2:L8	L2	=ROUND(D2/6*100,2)
M2:M8	M2	=ROUND(F2/9*100,2)
N2:N8	N2	=ROUND(H2/5*100,2)
O2:O8	O2	=ROUND(I2/12*100,2)
P2:P8	P2	=ROUND(J2/18*100,2)
Q2:Q8	Q2	=ROUND(A2-SUM(B2:P2),2)

 

[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]

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]

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]

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]

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]

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.