software

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

quot;Peter Fquot; wrote:

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


If your totals are in decimals

=FLOOR(A1,0.25)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=522884Hi 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

quot;daddylonglegsquot; wrote:

gt;
gt; If your totals are in decimals
gt;
gt; =FLOOR(A1,0.25)
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=522884
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=522884