mode

[zrx1200]

Trying to use mode on data with negatives times.

[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]-0:11[/TD]
[TD="align: center"]#value ![/TD]
[/TR]
[TR]
[TD]-0:06[/TD]
[TD="align: center"]#value ![/TD]
[/TR]
[TR]
[TD]-0:03[/TD]
[TD="align: center"]#value ![/TD]
[/TR]
[TR]
[TD]-0:05[/TD]
[TD="align: center"]#value ![/TD]
[/TR]
[TR]
[TD]0.01[/TD]
[TD="align: right"]0:08[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD="align: right"]0:00[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD="align: right"]0:01

[/TD]
[/TR]
[TR]
[TD]The #value is removing the seconds if present

formula [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]INT(CJ15*1440)/1440
I'm removing seconds as I thought it was affecting the mode function, but it in itself has the #value error.

So, do you need to condition times before mode will work with positive and negative times data set? [/FONT]

[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[zrx1200]

Update:

System NOT in 1904 apparently.

This formula is giving me the negatives and "text" as that's what I'm telling it do, duh....

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]IF(S13="Remove/Post",O13-T13,IFERROR(IF((Q13+"00:00:01")-O13<0,"-"&TEXT(ABS((Q13+"00:00:01")-O13),"h:mm"),(Q13+"00:00:01")-O13),""))

So if I put formula to this.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]IF(S13="Remove/Post",O13-T13,IFERROR(IF((Q13+"00:00:01")-O13<0,(Q13+"00:00:01")-O13,(Q13+"00:00:01")-O13),""))

+ numbers good
- numbers ############## which one would expect as excel no like negative times.

So, i then tried a helper column to do an ABS on the ########## cells to no avail.
[/FONT]_{^{<strike>
</strike>}}[/FONT]

 

[zrx1200]

It would appear that this is correct.

IT can be confirmed by attempting to change the format using the dropdown in the middle of the 'Home' tab on the excel ribbon. If the example value is the same under all of the formats listed in the dropdown then it is text, if it varies between the different formats then it is numeric.

Try this in a helper column to coerce the times into numbers, then get the mode from the helper.

=SUBSTITUTE(CJ15,"-","")*IF(LEFT(CJ15,1)="-",-1,1)

I think that should work as long as you have 1904 dates selected in advanced options. I've got it working, but not tested thoroughly.

edit:-

[h]:mm or h:mm should make no difference, it only affects how the times over 24 hours are displayed.

MODE will show N/A if there is no mode (no value that appears more than once).

So, had to modify your formula form this

=SUBSTITUTE(CJ15,"-","")*IF(LEFT(CJ15,1)="-",-1,1)

to this

=SUBSTITUTE(CJ15,"-","")*IF(LEFT(CJ15,1)="-",1,1)​

_{^{<strike>
</strike>}}​

Then for averaging and mode averaging for + and - numbers I did separate averages and mode just keeping in mined with are negative and positive.

Seems to work.

 

[jasonb75]

Try the helper formula formatted as number / general instead of time, that should allow mode and average to work with negatives.