Round it off
 

Hi,

I am creating estimates for a tast to be completed. The result comes in
mins. I have divided it by 60 to get it in hours. However, The result coming
is something like 1.42 hrs. 8.89 hrs etc.

What i want is that it should round it off to the nearest 15 mins and show
it. An hour is divided into .25 (15 mins); .5 (30 mins); .75 (45 Mins); 1 (1
hour)
Examples:
1.42 Hrs means 1 Hour and 21 Mins. (app) - This should show 1.5 hours
2.22 Hrs means 2 Hours and 13 mins - This should show as 2.25 hours
3.72 Hrs means 3 Hours and 42 mins - This should show as 3.75 hours

Something on these lines... is this possible? Round up and Round down
functions don't work for me as they round it to the no. of decimals we
specify and that does no good. I can't round it off to 0 decimals as it gets
the numbers wrong. I mean it might change 1 hour 5 min task to 2 hours. and
vice versa.

Thanks and Regards,
Sri Harsha Kiran.P

Round it off
 

The below returns number rounded up, away from zero, to the nearest multiple
of significance

=CEILING(A1,0.25)


If this post helps click Yes
---------------
Jacob Skaria


"Sri Harsha" wrote:

Hi,

I am creating estimates for a tast to be completed. The result comes in
mins. I have divided it by 60 to get it in hours. However, The result coming
is something like 1.42 hrs. 8.89 hrs etc.

What i want is that it should round it off to the nearest 15 mins and show
it. An hour is divided into .25 (15 mins); .5 (30 mins); .75 (45 Mins); 1 (1
hour)
Examples:
1.42 Hrs means 1 Hour and 21 Mins. (app) - This should show 1.5 hours
2.22 Hrs means 2 Hours and 13 mins - This should show as 2.25 hours
3.72 Hrs means 3 Hours and 42 mins - This should show as 3.75 hours

Something on these lines... is this possible? Round up and Round down
functions don't work for me as they round it to the no. of decimals we
specify and that does no good. I can't round it off to 0 decimals as it gets
the numbers wrong. I mean it might change 1 hour 5 min task to 2 hours. and
vice versa.

Thanks and Regards,
Sri Harsha Kiran.P

Round it off
 

<<round it off to the nearest 15 mins

=IF(MOD(A1,0.25)0.125,CEILING(A1,0.25),FLOOR(A1,0 .25))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

The below returns number rounded up, away from zero, to the nearest multiple
of significance

=CEILING(A1,0.25)


If this post helps click Yes
---------------
Jacob Skaria


"Sri Harsha" wrote:

Hi,

I am creating estimates for a tast to be completed. The result comes in
mins. I have divided it by 60 to get it in hours. However, The result coming
is something like 1.42 hrs. 8.89 hrs etc.

What i want is that it should round it off to the nearest 15 mins and show
it. An hour is divided into .25 (15 mins); .5 (30 mins); .75 (45 Mins); 1 (1
hour)
Examples:
1.42 Hrs means 1 Hour and 21 Mins. (app) - This should show 1.5 hours
2.22 Hrs means 2 Hours and 13 mins - This should show as 2.25 hours
3.72 Hrs means 3 Hours and 42 mins - This should show as 3.75 hours

Something on these lines... is this possible? Round up and Round down
functions don't work for me as they round it to the no. of decimals we
specify and that does no good. I can't round it off to 0 decimals as it gets
the numbers wrong. I mean it might change 1 hour 5 min task to 2 hours. and
vice versa.

Thanks and Regards,
Sri Harsha Kiran.P

Round it off
 

Another way
=ROUND(A1/0.25,0)*0.25

PS: If you are using Analysis ToolPak take a look at MROUND() function

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

<<round it off to the nearest 15 mins

=IF(MOD(A1,0.25)0.125,CEILING(A1,0.25),FLOOR(A1,0 .25))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

The below returns number rounded up, away from zero, to the nearest multiple
of significance

=CEILING(A1,0.25)


If this post helps click Yes
---------------
Jacob Skaria


"Sri Harsha" wrote:

Hi,

I am creating estimates for a tast to be completed. The result comes in
mins. I have divided it by 60 to get it in hours. However, The result coming
is something like 1.42 hrs. 8.89 hrs etc.

What i want is that it should round it off to the nearest 15 mins and show
it. An hour is divided into .25 (15 mins); .5 (30 mins); .75 (45 Mins); 1 (1
hour)
Examples:
1.42 Hrs means 1 Hour and 21 Mins. (app) - This should show 1.5 hours
2.22 Hrs means 2 Hours and 13 mins - This should show as 2.25 hours
3.72 Hrs means 3 Hours and 42 mins - This should show as 3.75 hours

Something on these lines... is this possible? Round up and Round down
functions don't work for me as they round it to the no. of decimals we
specify and that does no good. I can't round it off to 0 decimals as it gets
the numbers wrong. I mean it might change 1 hour 5 min task to 2 hours. and
vice versa.

Thanks and Regards,
Sri Harsha Kiran.P