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)

 

[Fluff]

Once you have formatted the column, then it should not change unless you already have something in your code that is changing the format.

 

[Joe4]

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 you do nothing, by default it will not show all those zeroes after the number like you see in columns P and Q.
The only way it shows all those extra decimals is if someone already re-formatted the column to show all those decimal places (it would NEVER show anything like that by default, someone must have done that).

If you click on any of those cells, and go to the cell formatting, you will see that someone set those decimal places to 15, or whatever it is.

 

[RAJESH1960]

Once you have formatted the column, then it should not change unless you already have something in your code that is changing the format.

Oh Yeah. I tried in a new sheet. Now it's working perfectly. Thanks Fluff.

 

[Joe4]

Oh Yeah. I tried in a new sheet. Now it's working perfectly. Thanks Fluff.

Like I said, that is not the default the behavior.
Somewhere along the way, someone told it to show that many decimal places.

 

[RAJESH1960]

Like I said, that is not the default the behavior.
Somewhere along the way, someone told it to show that many decimal places.

Yes. I had overwritten the formula on the cell which I had written a different formula earlier and hence it was not showing the correct answer. I learnt something new. You have to clear all the cell first, to clear the old format then enter the formula.