Function to pick Peak or Min from table of values in Excel

B

borourke

Is there a simple way to use a function to match the
maximum value to a position in Excel (or better yet
derivitives).

Example
Wavelength 400 500 600 700 800
%T 5.0 4.9 4.6 5.9 7.0

I would like to find the max %T's wavelength (800)
and the min %T wavelength (600)

Thanks
 
R

Ron Rosenfeld

Is there a simple way to use a function to match the
maximum value to a position in Excel (or better yet
derivitives).

Example
Wavelength 400 500 600 700 800
%T 5.0 4.9 4.6 5.9 7.0

I would like to find the max %T's wavelength (800)
and the min %T wavelength (600)

Thanks

With Wavelength in row 1 (A1:F1 in your example):

MaxT's wavelength: =OFFSET($A$1,,MATCH(MAX(T),T,0))
MinT's wavelength: =OFFSET($A$1,,MATCH(MIN(T),T,0))


--ron
 
J

Jason Morin

Assuming your data is in B1:F2, use this for max:

=INDEX(B1:F1,MATCH(MAX(B2:F2),B2:F2,0))

And for the min, just change the word "max" with "min".

HTH
Jason
Atlanta, GA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top