| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average & median of text "time" numbers
In A2:A9 are text "time" numbers which may cross midnight
1941 1852 0130 2347 0242 2326 0028 2257 Looking for formulas to place in A10:A11 which can return the average & median of the times in the same text "time" format Thanks -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
=TEXT(AVERAGE(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm") =TEXT(median(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm") both formulas are arrays so need to be enter with ctrl shift enter to work the crossing midnight bit is a bit unclear, if it affects the above, you will have to explain more. Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=572543 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
=TEXT(AVERAGE(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A $9,2),0)),"hhmm") =TEXT(MEDIAN(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A$ 9,2),0)),"hhmm") entered as array formulas (Ctrl+Shift+Enter) "Dav" wrote: =TEXT(AVERAGE(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm") =TEXT(median(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm") both formulas are arrays so need to be enter with ctrl shift enter to work the crossing midnight bit is a bit unclear, if it affects the above, you will have to explain more. Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=572543 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
Thanks, but I got #VALUE! with both Here's a sample with your suggestions in: http://cjoint.com/?irlpmeAXkb Av n median of text time numbers.xls the crossing midnight bit is a bit unclear, if it affects the above, The source times are in text, and are log-off times for consecutive days -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dav" wrote in message ... =TEXT(AVERAGE(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm") =TEXT(median(TIME(LEFT(A2:A9,LEN(A1)-2),RIGHT(A2:A9,2),0)),"hhmm") both formulas are arrays so need to be enter with ctrl shift enter to work the crossing midnight bit is a bit unclear, if it affects the above, you will have to explain more. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
=TEXT(AVERAGE(IF(TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2) ,0)0.5,TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2),0),TIME( LEFT(A2:A9,2),RIGHT(A2:A9,2),0)+1)),"hhmm") should work entered as an array shift ctrl enter I tested it based on your spreadsheet link and it worked I have said that if the finish time is after midday (0.5) it is a finish, if it is before midday then it is the next day, if this is the case i have added 1 to the value. I think this is what you want as an average Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=572543 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
Thanks, that returns some results but I'm not sure whether the results are
meaningful. I'll explain .. The source set of log-off times in A2:A9 are "scrambled", viz: 1941 1852 0130 2347 0242 2326 0028 2257 If I were to "sort" the times going by the earliest log-off time to the last log-off, it'll appear as: 1852 1941 2257 2326 2347 0028 0130 0242 So I believe the "average" log-off should be a figure between the earliest 1852 and the last 0242. Your formula returns: 1410 for the average which is out of range. For the median, think it should be the midpoint between the times 2326 and 2347. Your formula returns: 1916 for the median. Further insights appreciated .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Toppers" wrote in message ... =TEXT(AVERAGE(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A $9,2),0)),"hhmm") =TEXT(MEDIAN(TIME(LEFT($A$2:$A$9,2),RIGHT($A$2:$A$ 9,2),0)),"hhmm") entered as array formulas (Ctrl+Shift+Enter) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
Dav, Looks good, thanks! I replaced average with median (for the median
formula), and the result: 2336 gells with the clarification I posted to Toppers in the other branch. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dav" wrote in message ... =TEXT(AVERAGE(IF(TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2) ,0)0.5,TIME(LEFT(A2:A9,2),RIGHT(A2:A9,2),0),TIME( LEFT(A2:A9,2),RIGHT(A2:A9,2),0)+1)),"hhmm") should work entered as an array shift ctrl enter I tested it based on your spreadsheet link and it worked I have said that if the finish time is after midday (0.5) it is a finish, if it is before midday then it is the next day, if this is the case i have added 1 to the value. I think this is what you want as an average Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=572543 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
2336 is the median which would correspond to the midpoint between 2326 and 2347 2310 is the average this assumes the data is entered as text so 0028 is what is entered in the cell not 28 formated as 0000 how is your data entered in the spreadsheet. If it is numbers formated with leading 0's the formula will not work, but you said it was text in your initial post. if it is numbers =TEXT(MEDIAN(IF(TIME((B2:B9)/100,MOD(B2:B9,100),0)0.5,TIME((B2:B9)/100,MOD(B2:B9,100),0),TIME((B2:B9)/100,MOD(B2:B9,100),0)+1)),"hhmm") entered as an array shft ctrl enter Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=572543 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
Being in linear mode it was not clear who you were answering, so disregard my last post. Glad it is working, out posts must have crossed! Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=572543 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average & median of text "time" numbers
Yes, it did <g. Thanks again, Dav !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dav" wrote in message ... Being in linear mode it was not clear who you were answering, so disregard my last post. Glad it is working, out posts must have crossed! Regards Dav |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert numbers stored as numbers to text | Excel Worksheet Functions | |||
| Finding the median of numbers meeting criteria | Excel Discussion (Misc queries) | |||
| How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) | |||
| Sort or Filter option? | Excel Worksheet Functions | |||
| I enter numbers and they are stored as text | Excel Discussion (Misc queries) | |||
Linear Mode
