negative time as text

[zrx1200]

I have a column of negative times which I wish to sum and average.
formula that brings these figures is

IF(S738="Remove/Post",O738-T738,IFERROR(IF((Q738+"00:00:01")-O738<0,"-"&TEXT(ABS((Q738+"00:00:01")-O738),"h:mm"),(Q738+"00:00:01")-O738),""))

Now, as you see I'm putting the "-" in and text so its show as -0:00.

However if I use this

IF(S348="Remove/Post",O348-T348,IFERROR(IF((Q348+"00:00:01")-O348<0,ABS((Q348+"00:00:01")-O348),(Q348+"00:00:01")-O348),""))

it shows as a 0:00 and still as text as the figure is not right justified as number should be.

I assume I need to get the data out of text and negative in order to do any calculations??

 

[zrx1200]

Now here is something I don't understand. If I take this formula (same as first post) and put in new column it works? Why I wonder?

IF(S348="Remove/Post",O348-T348,IFERROR(IF((Q348+"00:00:01")-O348<0,ABS((Q348+"00:00:01")-O348),(Q348+"00:00:01")-O348),""))

Data becomes number and I can do calcs on them. Then I need to "convert" to negative showing again.

 

[zrx1200]

Now here is something I don't understand. If I take this formula (same as first post) and put in new column it works? Why I wonder?

IF(S348="Remove/Post",O348-T348,IFERROR(IF((Q348+"00:00:01")-O348<0,ABS((Q348+"00:00:01")-O348),(Q348+"00:00:01")-O348),""))

Data becomes number and I can do calcs on them. Then I need to "convert" to negative showing again.

Ended up using above in new column as a helper column to get results.