Roundt from hundreds to nearest whole Number

[Harold R.]

OHRM 303 Test01_05.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N
9	8:00		12:03		12:58		17:00						8.08
10	8:00		12:02		12:58		15:00						6.07
January_05

How can I round N to the whole Number if it is within.15 on either side of the whole number?

 

[Kimbu]

NN = if(abs(N-int(N))<=0.15,int(N),N)

 

[Harold R.]

Thanks it worked great.

 

[Harold R.]

OHRM 303 Test01_05.xls
	N	O	P	Q
10	6.57	6.57	0.00	2.00
11	11.03	11.00	3.00	2.00
January_05

Is there a way to make this formula round to the nearest .25?

 

[fairwinds]

ADVERTISEMENT

=MROUND(A1,0.25)

 

[Harold R.]

That didn't work I must be doing something wrong. I get a message of undefined in the pull down box.

 

[fairwinds]

ADVERTISEMENT

Analysis toolpack must be enabled.

(Tools - Add-ins...)

 

[Harold R.]

Thanks that did work but I am still not explaining what I need very well. If the Number in N is (Whole Number) plus or minus .12 I want it to round to Whole Number. What do I need to substitute to make it work.

 

[fairwinds]

???

But it does that.

 

[Harold R.]

This is a formula that is in another part of the worksheet before minutes are converted to hundreths:=ROUND(B9/(LOOKUP(MINUTE(B9),{0,15;8,1;23,15;38,1;53,15})/1440),0)*(LOOKUP(MINUTE(B9),{0,15;8,1;23,15;38,1;53,15})/1440) The rule I am trying to capture is if an employee sigs out 7minutes(or .12) before or after the hour the time is rounded to the nearest hour in hundreths.