CODEWITHSUNDEEP
excelexcel-2010forex
Brian White
Brian White

Reputation: 39

How can I create an average length of time in from multiple dates / times?

I am trying to calculate the average length of trades from a number of trades.

B10 thru B20 - are my entry dates and times formatted like 07/14/2014 04:00.

M10 thru M20 - are my exits dates and times formatted like 07/17/2014 08:00.

O10 thru O20 - are my lengths of trade which display as "3 Days 4 Hr 0 Min" using the following calculation =IF(M10="","",INT(M10-B10)&" Days "&HOUR(MOD(M10-B10,1))&" Hr "&MINUTE(MOD(M10-B10,1))&" Min")

I am having difficulties in combining the average of the 20 trades from O10 thru O20. I believe it is because of the above calculation format, but I do not know how to overcome this.

Anyone have any recommendations/suggestions as to how I can properly calculate the average length of time that I am in a trade over these last 20 trades?

Upvotes: 1

Views: 137

Answers (2)

barry houdini
barry houdini

Reputation: 46331

As you say, it's difficult to calculate the average from column O because those are text strings, you can simply use the average of the entry timestamps subtracted from the average of the exit timestamps, e.g.

=AVERAGE(M10:M20)-AVERAGE(B10:B20)

or to get formatted as per the column O format

=INT(AVERAGE(M10:M20)-AVERAGE(B10:B20))&" Days "&TEXT(AVERAGE(M10:M20)-AVERAGE(B10:B20),"h"" Hr ""m"" Min""")

btw you could use this simplified formula along the same lines to get your column O text string

=IF(M10="","",INT(M10-B10)&" Days "&TEXT(M10-B10,"h"" Hr ""m"" Min"""))

Upvotes: 3

Daniel Möller
Daniel Möller

Reputation: 86600

Create an alternative column P where you put the actual value of the lengths:

= M10 - B10

Average (Say it's P21):

= Average(P10:P20)

This value will be in days.

You can then take the hours (P22):

= (P21 - int(P21)) * 24

And the minutes:

= (P22 - int(P22)) * 60

And you can manage to mend all those formulas in a single one for your formatting

Upvotes: 0

Related Questions