How to quickly find the highest value in a table?

[Frank O]

I have a table of data imported from a csv file. There are 10 columns across the top. Down the side there are a couple of hundred rows representing dates/times.

For each of the 10 columns, I need to determine the date/time when its value was the highest. Of course I could plot a visual graph and look for peaks, but I'm hoping for a faster way to do this. Is there some trick or macro that would accomplish this very quickly? Thanks in advance.

[alansidman]

Please read the yellow banner at the top of this page.

[MrShorty]

Assuming data in A1:K200 and date/time stamps are column A:

1) B202 =MAX(B1:B200) copied into B202:K202
2) B203 =INDEX($A$1:$A$200,MATCH(B202,B1:B200,0)) copied into B203:K203. Format in desired date/time format, if Excel does not do it automatically.

Will that work?

[Frank O]

Thanks, MrShorty. I independently ran across the MAX() function, but was stuck on how to obtain the date/time for the peak value. I'll look into INDEX() and MATCH().

[MrShorty]

If it helps, MSFT has documentation for all of Excel's functions here (INDEX() and MATCH() are under lookup and reference functions): https://support.microsoft.com/en-us/...rs=en-us&ad=us

[Frank O]

Thanks again. One quick question:

B203 =INDEX($A$1:$A$200,MATCH(B02,B1:B200,0))

I'm scratching my head about "B02". Could that actually be "B202" (i.e. pointing to the result of the MAX() function in the row just above it)?

[MrShorty]

I'm scratching my head about "B02". Could that actually be "B202"

That is correct. I was careless in my proofreading. I will go back and fix it in post #3. Glad you were able recognize my error.

[Frank O]

Thank you! This should be a time-saver for me, and I appreciate the help.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Frank O]

Thanks for the tips, done.