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
Nothing attached. Click Start Upload after selecting the file.
2nd attemp!
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
I second the pivot table idea! 🙂
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.
