Excel - How do I calculate an average for both positive and negative numbers displayed as h:mm:ss

J49 to 89 contain the formula =IF(I57<0,TEXT(1-I57/24/60/60,"-H:mm:ss"),TEXT(I57/24/60/60,"H:mm:ss")) which gives a result ranging from -0:12:58 to 0:04:42.

I would like 2 things:

Firstly for cell B44 to contain the average from cells J49:J89. I've simply tried =AVERAGE(J49:J89) which gives [#DIV/0!]

And for cell F44 to give a total sum of every cell above 0:00:00 which I've tried with =SUMIF(J49:J85,">0") but this just results in 0.00

Thank you in advance to anyone that can figure this out.

1 Answer

Your formula

=IF(I57<0,TEXT(1-I57/24/60/60,"-H:mm:ss"),TEXT(I57/24/60/60,"H:mm:ss")) 

is returning a text value. Text won't work in AVERAGE or SUMIF for the sum part.

You will need to work with numbers instead. If you use time, the negative values will error.

Once you do the calculation, you can then convert that back to text (or time (if not negative)).