Rounding actual results - not just display

  • Thread starter Thread starter Akomic
  • Start date Start date
A

Akomic

Hi,

I've got a grid that says:

Hr Min
Maths: 5 25
English: 7 25

And so on.

I'm wanting to work out how much time is devoted to learning in the whole
week. What I've got so far is:

Hr Min
Total learning time: =A1+A2...
(counts up
all totals in
hours
column)

My current answer for hours is 18.

I'm stuck with what to do with the minutes column. so far, I've got an
invisible equation that works out the sum of all the minutes, in my case,
320. When I divide this by 60, to get the number of hours, it comes as
5.3333333

Whilst I know that this means 5 hrs 20 minutes, and therefore the total time
on my current timetable is 23 hr 20 minutes, I am at a loss as to how to
turn 5.333333 into a 5 that I can add to my hours column, and 20 that can go
into the minutes column.

In my mind, I need to somehow split the 5 from the .33333 - but all I can
find to do is to round it up, which impacts on the rest of my equations.
Also, if it had been 5.666666, my results would have been inaccurate, so I
need to actually split the integers from the decimals, and not simply round
them (unless I can specifically round down)

Hope this makes sense, and hope you can help me.

Ta loads,

Jon
 
Or maybe you could just format the cell as:

hh:mm
or
[hh]:mm

The one with the brackets will be useful if you exceed 24 hours.
 
Jon,

I'm a little at odds between your description and your example. I'll
presume your hours column is B, and your minutes column is C. The sum of
the minutes would be:

=SUM(C2:C20) (I guessed at the 20)

To drop the fractional part:

=INT(SUM(C2:C20))

For the remainder (left-over minutes):

=MOD(SUM(C2:C20),60)

So I guess your hours total would be:

=SUM(B2:B200) + INT(SUM(C2:C20))
 
Back
Top