Log In
Register
Home
March 29, 2023
Offline
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
See Answer
October 5, 2010
Offline
Nothing attached. Click Start Upload after selecting the file.
March 29, 2023
Offline
2nd attemp!
Trusted Members
October 17, 2018
Offline
My idea would be to add a column counting the number of highs, and two columns with the year and the month then see what that gives you,
Another and simpler option would be a pivot table
Trusted Members
February 13, 2021
Offline
I second the pivot table idea! 🙂
March 29, 2023
Offline
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)
