How to get the averages for Minutes

[Manny74]

Hello All,

So I have the following data, which is mostly in minutes:

00:01 Mins
00:05 Mins
00:04 Mins
00:03 Mins
00:07 Mins
00:04 Mins
02:32 2 Hours 32 Mins (152 Mins)

How do I calculate the average of minutes? (based on the data provided)
Currently from Format Cells, Number tab, the data is formatted as general...

 

[Joe4]

If it is displaying like that, while it is formatted as General, it means that your entries are entered as Text, and must be converted to numeric before you can do any math on them.

Here is one way.
Let's say that your data is in the range A1:A7. You can convert those text entries to minutes by placing the following formula in cell B1 and copying down to B7:

=LEFT(A1,5)*1440

Then, to get the average, just use this formula:

=AVERAGE(B1:B7)

 

[Manny74]

That worked, thanks!
From your formula, what does the 5 represent? 1440?

 

[Joe4]

The 5 is part of the LEFT function. We are pulling the 5 left-most characters in your cell, because that is where the time exists.

Regarding the 1440, it is important to understand how dates and time are stored in Excel.
Dates are stored as whole numbers, specifically, the number of days since 1/0/1900.
Time is just a fraction of one day (so 12 hours is equal to 0.5).
Since there are 1440 mintues in a day (24 hours times 60 minutes), if we want to convert the time fraction that Excel uses (which would be the fraction of one day), we need to multiple that fraction by the number of minutes in one day.

Hope that clarifies things.