Finding the time value closest to zero (with 1904 date system)

[Joe94]

I am trying to find a formula that, for each row, will display the value of the cell which is closest to 0. In order to use negative time values I have had to use the 1904 date system which I believe is disrupting the results when Excel calculates the minimum value. I have currently tried something like =MIN(ABS(A1),ABS(A2)) but this is not working. Does anyone have any suggestions as to how I can go about fixing this?

[TABLE="width: 310"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:00:50.799[/TD]
[TD]00:00:50.305[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:00:00.242[/TD]
[TD]00:00:00.000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-00:00:00.860[/TD]
[TD]-00:00:00.473[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-00:00:01.034[/TD]
[TD]-00:00:00.188[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-00:00:01.266[/TD]
[TD]-00:00:00.422[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-00:00:00.598[/TD]
[TD]00:00:00.409[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[Marcelo Branco]

Maybe

C1 copied down
=MIN(0+SUBSTITUTE(A1,"-",""),0+SUBSTITUTE(B1,"-",""))

format the results: hh:mm:ss.000

M.

 

[arthurbr]

What's wrong with =MIN(ABS(A1),ABS(B1)) ?
In your example,I replaced A2 with B1 as you stated you are looking for the closest to zero for each row
Or are they text looking like numbers?

 

[Joe94]

Thanks for your replies.

The values are currently already in the format hh:mm:ss.000. Although the values look as I have put above, in the formula bar the top row reads as:

[TABLE="width: 500"]
<tbody>[TR]
[TD]00:00:51[/TD]
[TD]02/01/1904 00:00:50[/TD]
[/TR]
</tbody>[/TABLE]

so when I press "Show Formulas" they become:

[TABLE="width: 450"]
<tbody>[TR]
[TD]0.000587951388888889[/TD]
[TD]1.0005787037037[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
which is why I assume the minimum function isn't working. As I said previously, the 1904 date format was used to allow negative time values.

Hope this clears things up.

Thanks

 

[Joe94]

Oh and I forgot to say but Marcelo your suggestion didn't work sadly.

 

[Marcelo Branco]

Oh and I forgot to say but Marcelo your suggestion didn't work sadly.

hmm...worked for me (my Excel version uses comma as decimal separator)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

00:00:50,799​

[/td][td]

00:00:50,305​

[/td][td]

00:00:50,305​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

00:00:00,242​

[/td][td]

00:00:00,000​

[/td][td]

00:00:00,000​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

-00:00:00,860​

[/td][td]

-00:00:00,473​

[/td][td]

00:00:00,473​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

-00:00:01,034​

[/td][td]

-00:00:00,188​

[/td][td]

00:00:00,188​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

-00:00:01,266​

[/td][td]

-00:00:00,422​

[/td][td]

00:00:00,422​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

-00:00:00,598​

[/td][td]

00:00:00,409​

[/td][td]

00:00:00,598​

[/td][/tr]
[/table]

M.

 

[Joe94]

There must be something wrong with my data then I'll have to investigate further. Thanks for your help anyway.

 

[shg]

This worked fine for me:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

00:00:50.799​

[/td][td]

00:00:50.305​

[/td][td="bgcolor:#CCFFCC"]

00:00:50.305​

[/td][td="bgcolor:#CCFFCC"]C2: =IF(ABS(A2) < ABS(B2), A2, B2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

00:00:00.242​

[/td][td]

00:00:00.000​

[/td][td="bgcolor:#CCFFCC"]

00:00:00.000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

00:00:00.860​

[/td][td]

-00:00:00.473​

[/td][td="bgcolor:#CCFFCC"]

-00:00:00.473​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

-00:00:00.860​

[/td][td]

-00:00:00.473​

[/td][td="bgcolor:#CCFFCC"]

-00:00:00.473​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

-00:00:01.034​

[/td][td]

-00:00:00.188​

[/td][td="bgcolor:#CCFFCC"]

-00:00:00.188​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

-00:00:01.266​

[/td][td]

-00:00:00.422​

[/td][td="bgcolor:#CCFFCC"]

-00:00:00.422​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

-00:00:00.598​

[/td][td]

00:00:00.409​

[/td][td="bgcolor:#CCFFCC"]

00:00:00.409​

[/td][td][/td][/tr]
[/table]

It might be easier to convert everything to decimal seconds.

 

[Marcelo Branco]

This worked fine for me:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

00:00:50.799​

[/TD]
[TD]

00:00:50.305​

[/TD]
[TD="bgcolor: #CCFFCC"]

00:00:50.305​

[/TD]
[TD="bgcolor: #CCFFCC"]C2: =IF(ABS(A2) < ABS(B2), A2, B2)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD]

00:00:00.242​

[/TD]
[TD]

00:00:00.000​

[/TD]
[TD="bgcolor: #CCFFCC"]

00:00:00.000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]

00:00:00.860​

[/TD]
[TD]

-00:00:00.473​

[/TD]
[TD="bgcolor: #CCFFCC"]

-00:00:00.473​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5​

[/TD]
[TD]

-00:00:00.860​

[/TD]
[TD]

-00:00:00.473​

[/TD]
[TD="bgcolor: #CCFFCC"]

-00:00:00.473​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

[/TD]
[TD]

-00:00:01.034​

[/TD]
[TD]

-00:00:00.188​

[/TD]
[TD="bgcolor: #CCFFCC"]

-00:00:00.188​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

7​

[/TD]
[TD]

-00:00:01.266​

[/TD]
[TD]

-00:00:00.422​

[/TD]
[TD="bgcolor: #CCFFCC"]

-00:00:00.422​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

8​

[/TD]
[TD]

-00:00:00.598​

[/TD]
[TD]

00:00:00.409​

[/TD]
[TD="bgcolor: #CCFFCC"]

00:00:00.409​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


It might be easier to convert everything to decimal seconds.

Weird, didn't work for me (comma as decimal separator)


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

00:00:50,799​

[/td][td]

00:00:50,305​

[/td][td]

00:00:50,305​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

00:00:00,242​

[/td][td]

00:00:00,000​

[/td][td]

00:00:00,000​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

-00:00:00,860​

[/td][td]

-00:00:00,473​

[/td][td]

#VALOR!​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

-00:00:01,034​

[/td][td]

-00:00:00,188​

[/td][td]

#VALOR!​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

-00:00:01,266​

[/td][td]

-00:00:00,422​

[/td][td]

#VALOR!​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

-00:00:00,598​

[/td][td]

00:00:00,409​

[/td][td]

#VALOR!​

[/td][/tr]
[/table]

M.

 

[shg]

Marcelo, try entering negative times as, for example

=-"00:00:00,473"