Find the percentage a cell is in a time range and the average time within each

I have cells with military times. I would like to find a) the percentage of entries made within each time range, and b) the average time of a range within those cells.

For example, if I have cells with the following times... 9:55, 10:00, 10:05, 11:00, 12:00, 13:00

In this example...
A) Since I only provide 1 entry, its 100%
B) (9:55+10:00+10:05) / COUNTA of 3 = 10:00

Are you actually still using Excel 2007? I can't remember if AVERAGEIFS was available in 2007, or SUMIFS, or COUNTIFS. If they were, you could use AVERAGEIFS or SUMIFS/COUNTIFS. If not, you'd probably need to use SUMPRODUCT.

Post a sample workbook, not a picture. See the yellow banner at the top of the page.

Thanks for the reply. I edited my Profile... using 2019.

I've uploaded a worksheet with some data. You'll find raw data on the DATA sheet and the time-related calculations to go in my RESULTS sheet.

Try:

Formula:

Please Login or Register  to view this content.

copied down.

You'll need to change 12:00-4:00 to 12:00-16:00

Be a lot simpler if you put the start and end times in separate cells.

I didn't say anything about the Entry Time. I was referring to the start and end times for the average time ranges.

PHP Code: 

Average
9:30-9:50
9:50-10:30
10:30-12:00
12:00-4:00 


I know you uploaded the spreadsheet. Do you think I could come up with a formula like that posted from scratch without opening the spreadsheet, creating the formula and testing it? Why do you think I suggested changing the last criterion?

Does that change anything about the code you presented?

I don't mean to be rude, but have you tested the formula?

If the start and end times for the time range criteria were in separate cells, you wouldn't need to mess about with TIMEVALUE, LEFT, RIGHT, LEN, and FIND. You could simply refer directly to the cells.

B5 is the cell with 9:30-9:50 in it. The formula goes in C5, next to it. That is, the column where you appear to have put all your other results. And yes, you put the formula in cell C5 and copy it down; the cell references will automatically adjust. Try it. Splitting the values was just a suggestion to simplify the formula. It isn’t essential. The formula posted works. Try it.

I'm confused. In my original workbook, I don't have the time ranges identified. Where is the formula getting the ranges from to compare the Entry Time with?

There may be some confusion. My original workbook only includes the Entry Time. It does not include the start and end time ranges, whether together or separate. The formula references col B; this col in the original workbook is the date for each row, not the time range.

Edit: Disregard the above... The formula belongs in the RESULTS sheet, and yes, that works. Thank you!!

Edit 2: There's something off with the formula or how its used, as the total percentage is off. I've attached the updated book with the formulas added.

Try this

=AVERAGEIFS($C$2:$C$98,$C$2:$C$98,">="&J2,$C$2:$C$98,"<="&$K2)

@kvsrinivasamurthy, I think I understand this, however, I'm looking to find the percentage the entries are within each of the time ranges noted in the RESULTS sheet.

Pl show in file how you calculate step by step and what is the result with good explanation.

This is one of your sample workbooks with various formulae added to demonstrate different approaches.

RESULTS (v2) shows the formula if the start and end time ranges are split.

Originally Posted by kvsrinivasamurthy

Pl show in file how you calculate step by step and what is the result with good explanation.

Thanks, though I don't see how I can use your formula overall. Maybe 'average time' is the inappropriate title for this post. Although I need to find the averages, I really want to discover the percentage of trades made during each time range, ie. X% of trades were made between 10:30-11:30.

Edit: I must apologize... I actually do want both the averages and percentages. I did not explain myself properly. That said, I'm still confused with how to get the average time for each range with your formula.

I strongly suggest you completely reword the title of the opening post in this thread, otherwise your helpers are on a wild goose chase.

Originally Posted by AliGW

I strongly suggest you completely reword the title of the opening post in this thread, otherwise your helpers are on a wild goose chase.

Helpful, thanks! I actually changed the title to include both.

Doh. Just realised ... it shouldn’t be shown as a percentage. It's an average time based on the time values within a time range. Format the values as Time.

That said, I do provide specifics as to what I'm looking for.

Our rules require you to summarise what you are trying to do in your thread title.

Originally Posted by JohnTopley

I cannot see the average you want is meaningful. To me, the only meaningful average is number of trades per hour (or 30 mins). When a trade occurred within any given time period is not (in my view) material.

Based on your answer, I wouldn't expect you to be a momo or event-based day trader. In such trading, there are clearly defined time periods. For example, the time period of 9:30-9:50 ET US is known as the 'morning drive', and it is critical to understand when within that time period most trades are made, especially if they are winning or losing trades. This in itself may not be a complete measure, but for example, I may have a harder time making meaningful trades right at the open... it's a psychological or emotional thing. In this case, I would need to shy away from making trades during this time... just me. Day trading I've found to be more of a psy/emotional rollercoaster than simply technical trading.

Edit: I should point out as well, that this exercise is about a specific type of trade, so finding what time is more profitable for ME is essential.

You've missed a time range out. See the updated examples attached.

How about adding these up ...

PHP Code: 

12.3893805310%
6.1946902655%
51.3274336283%
12.3893805310%
9.7345132743%
7.0796460177%
0.8849557522% 


And the number of trades is out by 7 because of the missing time range. Actually, probably just 6 bearing in mind my test entry at the end

Now, how can I find the average time within each range? @kvsrinivasamurthy provided a formula, but I'm not sure how to implement it for each of the ranges.

Have you actually looked at the examples in the files I've posted? Not only do they show the formulae for each calculation, they're colour coded so you can see which formula is in each cell.

The formulae I have posted give you EVERYTHING you have asked for. Just look at it.

Let's try again to spell it out ...

Note that any trades before 06:00 or after 16:00 will not be included in any of the calculations.

Originally Posted by TMS

Let's try again to spell it out ...

Ah, sorry you had to spell it out for me Thanks again!

Edit: Here's what I ended up with...

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Thanks for the rep.

Glad you have a solution.

I would feel more comfortable if B4 on the RESULTS sheet had the formula:

Formula:

Please Login or Register  to view this content.

copied down, so that there is a direct link with the TIMES sheet and the calculations performed in relation to the times. Otherwise, there is a risk you could change the ranges on the TIMES sheet and not update the RESULTS sheet.

Just my thoughts ... but, personally, I’d use the approach on RESULTS (v2) and avoid introducing a separate TIMES sheet. But, your workbook, your process(es), your choice.

This would be my preferred approach

You'd include DATA!J:J,"<>FBO" as another condition in the AVERAGEIFS, SUMIFS and COUNTIFS.

I think John was referring to the formula in G10 (at the time) containing

Formula:

Please Login or Register  to view this content.

hence the values going into the cell/formula were those in cells G4:G9 which added up to 100%

Re: JT

You do realize, you did not contribute anything to this thread... maybe next time.

I have to be honest and say that I don't think you contributed very much that was helpful to this thread. John was, at least, trying to help you, a stranger, despite you questioning his attitude.

Untested, but I would have thought this should work:

Formula:

Please Login or Register  to view this content.

That's actually what I initially tried, but it worked this time around

So, maybe you didn't ...

Originally Posted by TMS

Re: JT

I have to be honest and say that I don't think you contributed very much that was helpful to this thread. John was, at least, trying to help you, a stranger, despite you questioning his attitude.

Bull. His initial approach was to question what I wanted and why. A non-trader telling me what I should be looking for? How is that trying to help? I get it, JT is a long-time contributor, but rebutting the very thing I'm looking for is not 'contribution'.

Edit: You'll notice that I provided a lengthy explanation to JT. He questioned me again and I ignored it. He then brought it up once more, so yeah, bull on contribution.