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]

​

 

[jasonb75]

Before we can devise a solution we need to establish exactly what you have in your sheet.

How are you getting excel to accept the negative times?

Typically entry of negative dates and times is not accepted by excel unless you use some kind of workaround. Formulas that result in negative date or time values would show ######.

Are they formatted as text? Entered as decimal then formated as time using the 1904 date system? Or something else?

 

[zrx1200]

Before we can devise a solution we need to establish exactly what you have in your sheet.

How are you getting excel to accept the negative times?

Typically entry of negative dates and times is not accepted by excel unless you use some kind of workaround. Formulas that result in negative date or time values would show ######.

Are they formatted as text? Entered as decimal then formated as time using the 1904 date system? Or something else?

using

time using the 1904 date system​

 

[jasonb75]

You might need to upload a workbook with the problem to a file share site so that we can see exactly what you're working with.

I've just tried =INT(A1*1440)/1440 and MODE() with negative times without any problems when using 1904 dates.

The only way that I can reproduce the #VALUE ! errors is by having text times in the left hand column. If this is what you have in your actual file then there would need to be something to establish whether the times are in [h]:mm or [m]:ss format.

 

[zrx1200]

You might need to upload a workbook with the problem to a file share site so that we can see exactly what you're working with.

I've just tried =INT(A1*1440)/1440 and MODE() with negative times without any problems when using 1904 dates.

The only way that I can reproduce the #VALUE ! errors is by having text times in the left hand column. If this is what you have in your actual file then there would need to be something to establish whether the times are in [h]:mm or [m]:ss format.

Yes, figures do go left when negative signalng text correct? Format is of h:mm which I have cell format as, which I suppose will not actually represnt true "base" value, correct?

 

[zrx1200]

Small sample of, paste values (copy ,paste) into new worksheet.

So , I assume because of - showing that negative time is seen as text, correct? Which then causes an issue for Mode?

Even using the data sample below I get #N/A using Mode. I notice you used [h]:mm does this hold significant value in what we are trying to achieve?

[TABLE="width: 243"]
<tbody>[TR]
[TD="align: right"]0.002488426[/TD]
[/TR]
[TR]
[TD="align: right"]0.00255787[/TD]
[/TR]
[TR]
[TD="align: right"]0.009259259[/TD]
[/TR]
[TR]
[TD]-0:18[/TD]
[/TR]
[TR]
[TD]-0:18[/TD]
[/TR]
[TR]
[TD="align: right"]0.001956019[/TD]
[/TR]
[TR]
[TD]-0:03[/TD]
[/TR]
[TR]
[TD]-0:02[/TD]
[/TR]
[TR]
[TD]-0:11[/TD]
[/TR]
[TR]
[TD="align: right"]0.001666667[/TD]
[/TR]
[TR]
[TD]-0:02[/TD]
[/TR]
[TR]
[TD="align: right"]0.00693287[/TD]
[/TR]
[TR]
[TD]-0:15[/TD]
[/TR]
</tbody>[/TABLE]


ok does this help, when cell with negative time is clicked example -0:12 the formula tool bar shows that as is -0:12, but the positive shows as 12:12:00 AM.

 

[jasonb75]

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).

 

[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).

Ok, #N/A explained thanks, and that is why I was looking at truncating the seconds as there would never be the same time with seconds involved, but many on the h:mm situation.

ok, off to try helper idea......

 

[zrx1200]

Ok, #N/A explained thanks, and that is why I was looking at truncating the seconds as there would never be the same time with seconds involved, but many on the h:mm situation.

ok, off to try helper idea......

No like that formula.. #NA me error and yes all format show same when -, different when +

 

[zrx1200]

ok I missed the row number in formula. SO the - times such as -0:00 show, but anything > then -0:01 show ################.
+ are good.