I have a data set that includes several fields including Date, Time and Price. I would like to find the Max Price for each Date while showing the Time it occured at. I do not want to group by Time though, just display the data from the Row where the MAX Price occured. A small sample of data is attached. It shows the MAX High for Date 2019-07-18 was 121.218 and occured at Time 02:00 AM.Sample Data.ods
A reduced and anonymized sample file would be very helpful. To upload, please edit your input batch and use the paper clip icon. Thanks.
Hello Dubghall
I will try another proposal based on :
MAXIFS to find out the max value of a parameter for a single day
MATCH to get the line number where to find this max figure
INDEX to get the desired figure on the said line.
I checked with the sample data you gave us and it works. Please, let us know your own feed back on a larger set of data.
Here is the proposed spreadsheet with the desired figures in the range K7:U34
To show the community that the question has found its answer, please click on the ✓ aside the top of the correct answer and vote by clicking the ^ caret of all usefull answer.
Please do not use Add Answer but edit your original question to enhance the details of your question (answers are reserved for solutions to a problem on this Q&A site). Thanks in advance …
Kind regards, Michel
It appears a Pivot Table is not a viable solution. This method worked quite well on the full set of data.
Thank you very much.
I don’t think you can get the wanted results by a PivotTable.
You may use this tool, however, for the first steps.
See the attached example.
ask272211aSpecialMatchCompound_1.ods
But I think that with AutoFilter is easy.
Made with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.
Add Answer is reserved for solutions.
Press edit below your question if you want to add more information; also can comment an answer.
Check the mark (
) to the left of the answer that solves your question.