Hi,

I have a columns of numbers that represent minutes that when I total I
convert to hours and minutes. Now I want to round down the totals to the
nearest quarter hour, that is .25, .5 or .75. I have spent quite a bit of
time fiddling with the rounddown function but can't achieve that. Anyone
able to help?!?!?

thank you Peter

Since a quarter hour is 1/96th of a day, and 1 day is stored by Excel as 1,
try =rounddown(a1*96,0)/96, if a1 has your total.
--Bruce

"Peter F" wrote:

Hi,

I have a columns of numbers that represent minutes that when I total I
convert to hours and minutes. Now I want to round down the totals to the
nearest quarter hour, that is .25, .5 or .75. I have spent quite a bit of
time fiddling with the rounddown function but can't achieve that. Anyone
able to help?!?!?

thank you Peter

If your totals are in decimals

=FLOOR(A1,0.25)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522884

Hi daddylonglegs,

Well yes it's worked!! thanks very much I was looking at the wrong type of
formula and you have saved me.

By the way Bruce I tried your solution as well - but I think that's
multiplying by 96 and then dividing by 96, which means the figure does not
change. I inserted it just as you provided. Much appreciated both of you
for your quick response to my dilemma

Bye Peter

"daddylonglegs" wrote:


If your totals are in decimals

=FLOOR(A1,0.25)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522884

bpeltzer's solution

=rounddown(a1*96,0)/96

is quite valid, but it's based on having a time value in a1, so if a1
contains 12:36 it will return 12:30 (when formatted as a time).

You could also use rounddown where a1 is decimal, to round to the
nearest 0.25, i.e.

=ROUNDDOWN(a1*4,0)/4

although it does multiply and divide by the same number the trick is
that the rounding takes place between those operations so if a1 were
12.6....

a1*4 gives you 50.4, rounddown then rounds this down to the nearest
integer, giving 50, then this is divided by 4 giving 12.5 hence
rounding to the nearest 0.25

...although, of course, FLOOR is a bit neater for your type of scenario


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522884