Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How to average military times (EXCEL)
- Thread starter BigCatMCS
- Start date
SilentAiche
Technical User
BigCatMCS,
The Average function should recognize the time format and give you an answer in the same format. Using your example, [blue]=average(a1:a5)[/blue] returned an answer of 20:12 (which I manually verified as correct).
Hope this helps.
Tim
[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]
The Average function should recognize the time format and give you an answer in the same format. Using your example, [blue]=average(a1:a5)[/blue] returned an answer of 20:12 (which I manually verified as correct).
Hope this helps.
Tim
[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]
It depends on what your data really look like. If the values are times, then the simple average formula will work:
[tt]
=AVERAGE(A1:A5)
[/tt]
But, if the data are text then an array formula may be what you need:
[tt]
=AVERAGE(TIMEVALUE(A1:A5))
[/tt]
An array formula is entered by Ctrl+Shift+Enter. When done correctly it will appear with curly braces in the formula window:
[tt]
{=AVERAGE(TIMEVALUE(A1:A5))}
[/tt]
But you do not type the curly braces. Let Excel put them in for you.
[tt]
=AVERAGE(A1:A5)
[/tt]
But, if the data are text then an array formula may be what you need:
[tt]
=AVERAGE(TIMEVALUE(A1:A5))
[/tt]
An array formula is entered by Ctrl+Shift+Enter. When done correctly it will appear with curly braces in the formula window:
[tt]
{=AVERAGE(TIMEVALUE(A1:A5))}
[/tt]
But you do not type the curly braces. Let Excel put them in for you.
SkipVought
Programmer
FYI:
REAL TIME values are just NUMBERS. The UNITS of TIME is DAYS. Time is expressed in fractions of a day.
Right now in Texas, it is 3/30/2005 10:09, which has a Date/Time value of 38441.423: 38441 is the Date Value and .423 is the Time Value.
Skip,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...Neopolitan Blownapart!
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]
SilentAiche
Technical User
A note on array formulas: if you enter the formula and later edit it, you will have to again use Ctrl+Shift+Enter (Excel doesn't "remember" the curly brackets, it seems.)
Tim
[blue]__________________________________________________
If you need immediate assistance, please raise your hand.[/blue]
- Thread starter
- #6
AnotherHiggins
Technical User
Your 'times' are actually text!
Off to the right in an empty cell, type [1
copy that cell
highlight the cells containing your 'times'
right click, choose Paste Special
click the box beside multiply
press OK
Now the formula should work. You may need to reformat the cells (including the one with the formula) as time.
[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.
Off to the right in an empty cell, type [1
copy that cell
highlight the cells containing your 'times'
right click, choose Paste Special
click the box beside multiply
press OK
Now the formula should work. You may need to reformat the cells (including the one with the formula) as time.
[tt]_____
-John
[/tt]Help us help you. Please read FAQ181-2886 before posting.
- Thread starter
- #8
- Status
Similar threads
- Locked
- Question