mode

zrx1200

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

 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,148
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
592
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
7,148
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
592
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
592
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
7,148
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
592
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
592
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
592
ok I missed the row number in formula. SO the - times such as -0:00 show, but anything > then -0:01 show ################.
+ are good.
 

Forum statistics

Threads
1,081,532
Messages
5,359,359
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top