Rounding prices to two different decimals

[dev_86]

Hello,

I have a spreadsheet of about 5000 prices. I have increased prices by 5% but need these new prices to be rounded either to the nearest .50 or if higher than .51 then those need to be rounded to .95

Thank you!!

 

[Toadstool]

Hi Dev_86,

Does this do what you want?

Dev_86.xlsx
	G	H	I	J
1	Part	Price	New Price	New Rounded Price
2	X1846	$ 8.00	$ 8.40	$ 8.50
3	X1066	$ 10.00	$ 10.50	$ 10.50
4	X1717	$ 12.95	$ 13.60	$ 13.95
5	X1207	$ 22.25	$ 23.36	$ 23.50
6	X1944	$ 6.50	$ 6.83	$ 6.95
7	X1003	$ 12.50	$ 13.13	$ 13.50
8	X1081	$ 18.95	$ 19.90	$ 19.95
9	X1478	$ 33.00	$ 34.65	$ 34.95
10	X1171	$ 32.80	$ 34.44	$ 34.50
11	X1473	$ 31.00	$ 32.55	$ 32.95
12	X1950	$ 30.95	$ 32.50	$ 32.50
13	X1197	$ 30.96	$ 32.51	$ 32.50
14	X2233	$ 30.97	$ 32.52	$ 32.95
Sheet1

Cell Formulas
Range	Formula
I2:I14	I2	=H2*1.05
J2:J14	J2	=IF(I2-INT(I2)>0.51,INT(I2)+0.95,INT(I2)+0.5)

 

[dev_86]

You’re awesome, thank you!!

 

[Toadstool]

You’re awesome, thank you!!

You're welcome!
Thanks for the feedback.

 

[Dave Patton]

Since you have Excel 365, try the following

Let_a.xlsm
	G	H	I
1	Part	Price	Rounded
2	X1846	8	8.50
3	X1066	10	10.50
4	X1717	12.95	13.95
5	X1207	22.25	23.50
6	X1944	6.5	6.95
7	X1003	12.5	13.50
8	X1081	18.95	19.95
9	X1478	33	34.95
10	X1171	32.8	34.50
11	X1473	31	32.95
12	X1950	30.95	32.50
13	X1197	30.96	32.50
14	X2233	30.97	32.95
2e

Cell Formulas
Range	Formula
I2:I14	I2	=LET(n,H2*1.05,i,INT(n),IF(MOD(n,1)>0.51,i+0.95,i+0.5))