specific round figure

[abdulla88]

I have a question and I hope to get an answer from you.

I have grades for some students and I want to make a round figure for the marks in a special way as follows:

• If the figure in B1 contains 0.1 the round figure should be same 0.1
• If the figure in B1 contains 0.2 the round figure should be same 0.2
• If the figure in B1 contains 0.3 the round figure should 0.5
• If the figure in B1 contains 0.4 the round figure should 0.5
• If the figure in B1 contains 0.5 the round figure should be same 0.5
• If the figure in B1 contains 0.6 the round figure should 1
• If the figure in B1 contains 0.7 the round figure should 1
• If the figure in B1 contains 0.8 the round figure should 1
• If the figure in B1 contains 0.9 the round figure should 1

I hope that I have explained the question clearly, awaiting your answers, For more details please check attached file.



Thank You

Round figures.xlsx
	A	B	C
1	student	Mark	Correct fourm
2	A	50.1	50.1
3	B	50.2	50.2
4	C	50.3	50.5
5	D	50.4	50.5
6	E	50.5	50.5
7	F	50.6	51
8	G	50.7	51
9	H	50.8	51
10	I	50.9	51
Sheet1

 

[jasonb75]

Based on your examples,

*** formula edited to allow for floating point precision error noticed after original was sumbitted ***

Book1
	A	B	C	D
1	student	Mark	Formula	Correct fourm
2	A	50.1	50.1	50.1
3	B	50.2	50.2	50.2
4	C	50.3	50.5	50.5
5	D	50.4	50.5	50.5
6	E	50.5	50.5	50.5
7	F	50.6	51	51
8	G	50.7	51	51
9	H	50.8	51	51
10	I	50.9	51	51
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=IF(ROUND(MOD(B2,1),1)>0.2,CEILING(B2,0.5),B2)

 

[MrTomasz]

try this
=IF(INT(RIGHT(b2;1))<3;b2;IF(INT(RIGHT(b2;1))<6;LEFT(b2;LEN(b2)-1)&"5";LEFT(b2;LEN(b2)-2)+1))