

March 29, 2023

Hello,
I have attached a table of 3 months worth of stock data which shows date, time (hours) and high (price).
I'd like to count how many times the max high (ie the high of the day) occurs for each hour over the 3 month period.
How would I go about this?
Thanks


Trusted Members

October 17, 2018



Trusted Members

February 13, 2021



March 29, 2023

Ok, so here’s what I came up with.
First, I made a pivot table with the date in rows and time (hours) in columns. Max of high was used for values. This gave a table with subtotals showing the max high for each day.
Then I made a second table referencing the pivot table with hours for column headers and used an IF statement to return 1 if each hour’s high equalled the max high or 0 if it did not.
I then summed each column to show how many times each hour had the max high over the given period.
I don’t know if this was the most elegant / efficient way of going about this but it did work.

Answers Post
1 Guest(s)
