Find the average for a time value

you will have to use a slightly different method which (in my opinion) is probably easier.

The reason you cannot use the times directly is because Numbers only has a data for date/time (in addition to the other types of string, number, duration). When you do not enter a complete date/time value (an entry that includes both the date and the time in the proper format) Numbers adds the missing part for the day and time you added the value. This means the while you want to enter only the time, then date will change and this will make the average be computed incorrectly for your purposes.

Here is is what I propose:

Enter the Hours in column A and the minutes in column B.

I left a column for seconds but right now assume you are not using that.

make the first two rows ahead rows

D3=IF(COUNTA(A3:B3)>1, NUMTOBASE(A3,10,2)&":"&NUMTOBASE(B3,10,2), "")

this is shorthand for... select cell D3, then type (or copy and paste from here) the formula:

=IF(COUNTA(A3:B3)>1, NUMTOBASE(A3,10,2)&":"&NUMTOBASE(B3,10,2), "")

E3=IF(COUNTA(A3:B3)>1, 60×A3+B3, "")

to fill down,

select cells D3 and E3, copy

select cells D3 thru the end of column E, paste

E1=AVERAGE(E)

D2=NUMTOBASE(INT(E1÷60),10,2)&":"&NUMTOBASE(ROUND(E1−60×INT(E1÷60), 0),10,2)

The easiest way I've found to do this in Numbers involves the use of one extra column (which can be hidden) and three simple formulas.

The formula in B2, copied down the column is =TIMEVALUE(A)

The bottom row is defined as a Footer Row (important).

The formula in A8 is =TODAY()+B8 .

A8 is formatted to hide the date:

The formula in B8 is =AVERAGE(B)

That's it! Should be easy to do on an iPhone.

SG

Hi Kevin,

Just tested the approach on my iPhone, adding a new "time" when the bus is late. The simple solution I posted above works well!

If you use take advantage of the built-in Numbers ability to parse date-time strings and the powerful but simple date-time and "duration" functions in Numbers then you really don't have to get involved in the underlying math, unless you like that sort of thing!

Here, I just formatted the body cells in Column A as Text, and Numbers is smart enough to figure out what time I meant. Many other formats (24-hour, or using 5:30a or 5:30 am or 5:30p, etc., etc.) are recognized automatically.

SG

Hi Kevin,

Yes, you can "just input these time values and then tell Numbers to find the average of all of them," but you won't get the result you are expecting.

Any cell where a time of day has been entered, and any cell where a date has been entered actually contains a Date & Time value identifying a specific instant in time. If only the time part has been entered, the date part is set to the date on which the entry was made; if only the date part has been entered, the time part is set to 00:00:00 (midnight, at the beginning of that day).

The AVERAGE of your entries over the last six months would be correctly calculated as some time near the middle of June 2016. Correct, but not the average you want.

Numbers needs to be told what to do to get from your list of entries (a collection of Date&Time Values) to the result you are looking for (the average value of the time parts of those Date&Time Values). That's where all that 'stuff' comes into play.

Once Numbers has those instructions, you can make a simple entry, let Numbers do all the rest of that stuff to convert your entries to values that can be used to calculate the result you want, then present that result in the form you want.

When you get off the bus tomorrow at 5:02, what do you want to enter?

5:02

5:02 AM

5:02 PM

5.02

502

"As in, over the last six months..."

Do you want to calculate the average over ALL entries, or over a maximum period of 'the last six months' or some other maximum period?

Are you starting fresh, or do you have existing date to be entered into the table? If there is existing data, what is the format of that date (see 5:02 list above).

What version(s) of Numbers are you using? (For version numbers of Numbers for Mac, see About Numbers in the Numbers menu).

Regards,

Barry

This is sweet, SG. Nicely done.

quinn

Thanks, quinn!

SG

Mikey likes it!

(So does Barry.)

Nicely done!

Regards,

Barry

Thanks, Barry! I assume this isn't the Mikey you are referring to.🙂

SG

You're welcome.

No,this one, from several years earlier. 🙂

Can't say I liked what Mikey liked then, though.

Barry