Round function with floor

[meghamadhulika]

i calculating some late arrivals in my company.
A person if late for 4 times, its half day i.e.- penalty 0.5
A person if late for 6 times, its full day i.e. - penalty 1.0
so I have been using the FLOOR function for the same.
for e.g. A- Number of Late Arrivals
B- Number of days penalty
with the formula-FLOOR((IF(A1<=4,(A1/4*0.5),(A1/6))),0.5)

Now my problem is, I want answers in only multiples of 0.5 but in cell B1 the answer should be 2 instead of 3.
As after every 6 days, penalty is 1 day but if we are left with 3 days of Late arrival, it should be reduced to 0 than rounded to 1.

[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD].5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]17[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]13[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Please help me with this.

Regards,
meghamadhulika

 

[FormR]

Hi, welcome to the forum!

Is it this maybe:

Excel 2013
	A	B
1	15	2
2	4	0.5
3	17	2.5
4	13	2
Sheet1

Cell Formulas
Range	Formula
B1	=INT(A1/6)+(INT(MOD(A1,6)/4)/2)

 

[meghamadhulika]

Wow, This worked Like Magic.
Will you please explain me how it works???

Best Regards,
meghamadhulika

Hi, welcome to the forum!

Is it this maybe:

Excel 2013

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]15[/TD]
[TD="bgcolor: #FFFF00, align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.5[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]2.5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=INT(A1/6)+(INT(MOD(A1,6)/4)/2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Great, good to hear.

Will you please explain me how it works???

Try looking at the built in help for each of the functions being used and also stepping through the calculation steps using the "Evaluate Formula" option on the "Formulas" tab.