Excel Using Functions

You won't be able to do it in the exact way you are trying to.

If I read a little into your question, you are using the data entered (in the format hh:mm) to mean time spent in a given activity or time taken for a given process to complete.

Excel does not recognize the data the same way. While the format is the exact same, Excell recognizes this as a time of day. As long as the data entered is <24:00, you can use the AVERAGE function and answer will come out correct. Any number greater than 24 confuces Excel as there are only 24 hours in a day.

Therefore I think you will need to change the format of your data. Either enter the data as a number of minutes (1:30 = 90) or as fractions of an hour (1:30 = 1.5). Format the cells as a normal number. Then the AVERAGE function will work properly.

Not sure what you mean by "range." If you mean rank (i.e. size relative to other values in the column), there is a RANK function whereby you specify the cell to be ranked, the range of cells to be included in the ranking (you must use absolute references for this to work properly when copying the formula into a range of cells), and sort order (ascending - 1 = smallest number; or descending - 1 = largest number).

Hope this answers your question.

Average does work, but format your cells as:
[hh]:mm
to allow for over 24 hours.

See:

www.cpearson.com/excel/datetime.htm#TOC

for more information on using dates in Excel

This question was closed by the author