I'm trying to work out average times over a month. The problem is each day starts at 22:00 and finishes about 17:00 the following day. Using =Average(A1:A31) will not produce the correct result as this will be for 00:00 to 23:59.
How can I tell Excel that the day starts at 22:00 and finishes at 17:00 so the correct average times is given.
NB: The times for each day are for when an event has finished.
Without using helpers you could possibly use:
=AVERAGE(IF(A1:A31<>"",MOD(A1:A31-"22:00",1)))
Entered as an array using CTRL + SHIFT + ENTER
or Non CSE Array option
=SUMPRODUCT(--(A1:A31<>""),MOD(A1:A31-"22:00",1))/COUNT(A1:A31)
Last edited by DonkeyOte; 03-07-2009 at 04:48 AM. Reason: Incorrect Range
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks