Help on Simple Formula

[srinivas.sandan]

Dear All,

Good day to everyone.

I have 2 values in 2 coloumns and the result in 3 coloumn. I am trying to subtract the values in 2nd coloumn from the values in 1st coloumn. But when the values in coloumn 2 are greater than coloumn 1 it return a error. Please find the snap shot below:

<TABLE style="WIDTH: 162pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=216 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" span=3 width=72><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=72 height=17>

A​

</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>

B​

</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>

Result​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="0.23402777777777781">

5:37​

</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.20833333333333334">

5:00​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="2.5694444444444464E-2" x:fmla="=A2-B2">

0:37​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="0.23958333333333334">

5:45​

</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.23958333333333334">

5:45​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0" x:fmla="=A3-B3">

0:00​

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num="0.24652777777777779">

5:55​

</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="0.26041666666666669">

6:15​

</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="-1.3888888888888895E-2" x:fmla="=A4-B4">

#####​

</TD></TR></TBODY></TABLE>

Kindly help me with a formula to avoid the error ans show the positive numbers positive and negetive numbers negative.

Thanks in anticipation.

Warm Regards,
Sandan

 

[schielrn]

So do you want it to show up as -0:20 or 23:40?

 

[rs2k]

Are these in time/date format?

 

[schielrn]

Since you made a reference about negative numbers try:

=IF(B1>A1,"-" & TEXT(B1-A1,"[h]:mm"),A1-B1)

 

[Jonmo1]

How about

=ABS(B1-A1)

 

[srinivas.sandan]

So do you want it to show up as -0:20 or 23:40?

Thansk for your time. I would like to display it as -0:20.