Max Functions

[raweber]

Is there a way to return the row number of a matrix has the maximum or minimum value of that column? I'm not looking for the value, but the row number. I want to tie this value to a text string to display on my spreadsheet.

[swatsp0p]

Robert: Try this (adjust range to meet your needs):

=MATCH(MAX(K4:K21),K:K,0)

HTH

Bruce

[raweber]

Thanks, Bruce. This gets me going in the right direction. Unfortunately it only works for a 1-dimensional array and mine is 2-d. I think that I can make it work with a series of nested IFs, but that sounds tedious.

Thanks again,

Rob

p.s. - Love your sig.

[swatsp0p]

About my sig... a year ago I would have been able to discern that you had a 2D range...


Bruce

[raweber]

Ran into trouble with the nested IFs. I have 12 columns of data (one If for each column) and the limit for nesting is 7. How about this:

I've given each column a name (month1, month2, etc.). I have created a cell that generates the correct array name in the cell from the given data. Is there a way to reference that cell in the Match function as an array name (and therefore reference the correct array) rather than simply the entry in that cell?

[Ron Coderre]

Please excuse me for jumping in here, but I think I might have something you can work with.

Assuming you have a rectangular range of cells that contain numbers and you want to know the row numbers of the cells that contain the maximum and minumum values within that range, try these formulas:

MaxValRow: =SUMPRODUCT(($A$1:$C$10=MAX($A$1:$C$10))*ROW($A$1:$C$10))

MinValRow: =SUMPRODUCT(($A$1:$C$10=MIN($A$1:$C$10))*ROW($A$1:$C$10))


I used the range A1:C10, but you can adjust the references to fit your situation.

Does that help?

Ron

[swatsp0p]

Thanks for the suggestion, Ron. I figured it would be a SUMPRODUCT solution, but I am still trying to figure out that one!

One note: If you have multiple MAX or MIN values, this will skew the result.

e.g. if the MAX appears in row 2 and again in row 10, the result of the formula will be 12.

Rob: Is this an issue for you (duplicate entries in your table)? If so, you could test for duplicates and indicate a failure by this:

=IF(COUNTIF(K4:N21,MAX(K4:N21))>1,"FAIL",SUMPRODUCT((K4:N21=MAX(K4:N21))*ROW(K4:N21)))

Bruce

[raweber]

For all intents and purposes, the numbers in the table show a peak somewhere near the lower right-hand corner. Each row peaks towards the bottom and there is an overall peak around row 9 or 10. I am NOT looking for the absolute max, but rather the max in a user specified column.

What I'm trying to do is allow the user to specifiy a condition (column), and have the spreadsheet return the max value in that column (tied to some other user inputs). Then I want the spreadsheet to return a text string that is unique for every entry in the array (I've set up a second array with the text string in it - therefore the requirement for the row number for the max value).

Ron, by all means, jump in - and anyone else, the water's fine. I'll play with your suggestion, Ron, and see if it's workable for my situation.

Rob

[raweber]

No, I'm getting a combined value of some sort.

Is there anything with the call name/label possibility?

[Ron Coderre]

Well, given what we know so far, maybe you can work with a variation of this formula:
MaxValRow:
=SUMPRODUCT((OFFSET($A$1:$A$10,,?)=MAX(OFFSET($A$1:$A$10,,?)))*ROW(OFFSET($A$1:$A$10,,?)))

MinValRow:
=SUMPRODUCT((OFFSET($A$1:$A$10,,?)=MIN(OFFSET($A$1:$A$10,,?)))*ROW(OFFSET($A$1:$A$10,,?)))

Note: the ? in the OFFSET function indicates how many columns to shift from A:A. Zero indicates use A1:A10, 3 indicates use D1:D10.

Are heading in the right direction here?

Ron

[raweber]

I added the Match function to the bottom of each column. Then I used a nested hlookup to pull the text string I wanted from the secondary matrix.

Thanks for all your help.

Rob

[swatsp0p]

Way to go Rob. I'm glad you got it figured out. Ron, thanks for bailing me out on this one...

Bruce