max value in column and related time

[Mollymm]

I have 2 columns of numbers, A is time and B is a list of numbers.
I need to find the find and record the number in column B that is the greatest diffference from 0, it may be positive or negative. Then report the corresponding time from column A.

Thank you for helping.

 

[steve the fish]

Try this. As with vlookup it will return the first value in event there are two the same. Has to be entered CNTL-SHIFT-ENTER. Works for 100 cells each column. If you need any more adjust.

=INDEX(A1:A100,MATCH(MAX(ABS(B1:B100)),ABS(B1:B100),0))

 

[Mollymm]

Thank you for the help.
When I enter this I am getting the time (from column A) which I need, but not the corresponding (max) number from column B

 

[steve the fish]

This will do that:

=MAX(ABS(B1:B100))

 

[Mollymm]

ADVERTISEMENT

Hopefully one last question, thank you for your patience.

=MAX(ABS(B1:B100)) gives me the max abs, yes. If the number is negative I loose that information, is there a way to regain it?

 

[steve the fish]

Try this:

=IF((MAX(B1:B100)+MIN(B1:B100))>0,MAX(B1:B100),MIN(B1:B100))

 

[Mollymm]

Thank you so much!