Find index where max value occurs

[strokebow]

Hi,

I am working on UDF.

I want to find the maximum value in column Y. But I also want the index at which the maximum value is found such that I can also find the corresponding X value.

You see X represents time and Y represents signal. So I want to find the x value at which the Y signal is maximum. But I also would like to know the index within the column at which these values occur.

Can anyone please offer help/advice?

My code is below.

Thanks

Please Login or Register  to view this content.

[efernandes67]

Hi,

Can you post a workbook with an example?

Elio Fernandes

[mohd9876]

you don't need a UDF you can use a Match and Index.

For Example:
=INDEX(A1:A8,MATCH(MAX(B1:B8),B1:B8,0))

[strokebow]

Hi Thanks for your replies.

I realize that you can do this specific thing without VBA but I need to get this data to proceed with my UDF.

Example data would be:

X Y
1.1 1000
1.2 2300
1.3 1100
1.4 507
1.5 2000
... ...

e.g.) Max in Y is 2300. This is when X = 1.2
This is the 2nd element of the column of input data.

How do I do this in VBA?

Cheers

[mohd9876]

Please Login or Register  to view this content.

[mohd9876]

if you want only the index without the values just eliminate the index function:

Please Login or Register  to view this content.

[snb]

To find the value in column 7, corresponding to teh row of the maximin in column6:

Please Login or Register  to view this content.