mode

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
601
Trying to use mode on data with negatives times.

-0:11#value !
-0:06#value !
-0:03#value !
-0:05#value !
0.010:08
0.000:00
0.000:01

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]


<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,579
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Apr 14, 2010
Messages
601
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

Well-known Member
Joined
Dec 30, 2008
Messages
9,579
Office Version
2019
Platform
Windows
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

Well-known Member
Joined
Apr 14, 2010
Messages
601
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

Well-known Member
Joined
Apr 14, 2010
Messages
601
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?

0.002488426
0.00255787
0.009259259
-0:18
-0:18
0.001956019
-0:03
-0:02
-0:11
0.001666667
-0:02
0.00693287
-0:15

<tbody>
</tbody>


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.
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,579
Office Version
2019
Platform
Windows
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).
 
Last edited:

zrx1200

Well-known Member
Joined
Apr 14, 2010
Messages
601
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

Well-known Member
Joined
Apr 14, 2010
Messages
601
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

Well-known Member
Joined
Apr 14, 2010
Messages
601
ok I missed the row number in formula. SO the - times such as -0:00 show, but anything > then -0:01 show ################.
+ are good.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,354
Messages
5,468,122
Members
406,568
Latest member
vvijay92

This Week's Hot Topics

Top