Find location of max value in a Table 1

[dmanleygen]

[red]Hello,[/red]

I have a table of data in Excel.
I want to find the location of a value based on a formula.
I hope to be able to return the "x,y" coordinates of the value calculated (in my example its the time and location of the max temperature during a cycle of temperature mapping).

If any body could help me with this that would be appreciated,

[red]Thank you,
Derry Manley[/red]

 

[SkipVought]

Hi,

Use the Match function to return the row/column offset.

What does your data look like?


When you say x,y coordinates, are you referring to cell coordinates or pixels?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[dmanleygen]

What I have is something like this:

TC1 TC2 TC3
a 10 20 20
b 15 25 [red]35[/red]
c 5 10 20

What I want to do is look up the table and find the coordinates of a value.
In this example max value is 35.
What I hope to get is:
Max = 35
Location = TC3
Time = b

Does this clear it up?

I tried Match but it only looks up one row or a column I was hoping to be able to look up an array?

 

[SkipVought]

paste this procedure in a MODULE. Use as you would a spreadsheet function.
sVal: is the lookup value (cell reference)
rng: is the lookup range (in your example B24)

Function Coords(sVal As String, rng As Range)
    Dim r As Range, b As Boolean
    Application.Volatile
    b = False
    For Each r In rng
        With r
            If .Value = sVal Then
                Coords = r.Row & "," & r.Column
                b = True
                Exit For
            End If
        End With
    Next
    If Not b Then Coords = "0,0"
End Function

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[dmanleygen]

Thank you!

I have 26 excel sheets with thermocouple data from a sterilisation cycle.
17 thermocouples x 5 sec logging over about 3 hours.
I need to find where and when the max and min happen

[red] Thank you![/red]

 

[dmanleygen]

Would I be asking too much if I wanted to find the locations of all, say, 20s in the example given?

I have a basic understanding of programming but I'm not the best at implementing them!

What I believe is required is a loop that would "remember" the previous value and exclude it in the next pass through. Could it be done row by row then column by column or could it be preformed in one go?

If this is outside the reach of free advice just say so and I'll plod away through it till I get something working!


SkipVought, I've just made myself a little sheet that we can copy in information and a table automatically gets updated with min, max and some other simpe stuff, ready for printing.
You have saved me so much time searching through data that I don't know what to say!

But thank you again.

Derry
Validation Engineer,
Pharmaceutical Industry

 

[SkipVought]

nope!

To visually identify occurrences of a specified value, I'd use Format/Conditional Formatting.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[dmanleygen]

How would you compile these visual results with their coordinates in a list somewhere?

Back to my work: what I'm trying to do is show, in a tabular summary sheet, how often and when the temperature went above a set point or identify the specific duration that a thermocouple was above a set point.

The problem is that we use conditional formatting in most cases but as things are growing and more and more data in coming in, we're running at the limits of what normal (simple) excel can do.
I'm trying to develop procedures that will allow people to do this work themselves without getting in third part software or help.
It would mean that people can do this work from any PC that would have Excel on it.
It would also mean that I don't get as many excel sheets to work on, when all I seam to be doing is formatting!

 

[SkipVought]

couldn't.

how 'bout this?

Function Coords(sVal As String, rng As Range, Optional iOccur As Integer = 1)
    Dim r As Range, b As Boolean, i As Integer
    Application.Volatile
    b = False
    For Each r In rng
        With r
            If .Value = sVal Then
                Coords = r.Row & "," & r.Column
                b = True
                If i >= iOccur Then Exit For
                i = i + 1
            End If
        End With
    Next
    If Not b Then Coords = "0,0"
End Function

if you want any other than the first occurrence, enter as as optional value

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[dmanleygen]

I don't know how you come up with these so quickly!

With my basics in home learned programming I'll be able to get exactly what I want!

Thank you,

Derry