Address for max value in noncontiguous range

[Chuck64]

I have a non contiguous range and I want to find the cell address of the max value. I used =Max(UsedInv) to find the maximum value. But I cannot find out how to get the address of that cell.

Chuck

[Bob Phillips]

What are the cells in the range, is there a pattern?

[Simon Lloyd]

You need to lengthen your formula to incorporate ADDRESS, ROW & COLUMN.

Use this:
=ADDRESS(MAX((UsedInv=MAX(UsedInv))*ROW(UsedInv)),MAX((UsedInv=MAX(UsedInv))*COLUMN(UsedInv)),4)

It's an array formula, array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)

[DonkeyOte]

Simon, I'm not sure that would work with a non-contiguous range.

As Bob is angling - if there's a pattern that's one possibility - another might be based on the Areas within the non-contiguous range.

[Simon Lloyd]

I assumed that UsedInv was the named range and therefore contiguous, maybe i missed something!

EDIT: Yep your right i just created a non contiguous named range and it failed!

[DonkeyOte]

FWIW, if there's no pattern.... you could in theory return the co-ordinates in the form of:

=MIN(IF(($A$1:$C$100=MAX(UsedInv))*(COUNT(1/(ERROR.TYPE(UsedInv $A$1:$C$100)=1))=0),ROW($A$1:$A$100)+(COLUMN($A$1:$C$1)/100000)))
confirmed with CTRL + SHIFT + ENTER

in the above it is assumed that A1:C100 contains all cells used within the non-contiguous named range - modify as appropriate

You can convert the above "co-ordinate" to an Address in an adjacent cell - ie assume the above formula is in E1:

F1: =ADDRESS(INT(E1),ROUND(MOD(E1,1)*100000,0))

[Simon Lloyd]

DO i'm no formula Guru but wouldn't something of this nature work?
=CELL("address",(LARGE(CHOOSE({1,2,3},A1:B14,D1:D22,G9:H21),1)A1)
Assuming the OP knows the non contiguous ranges.

[DonkeyOte]

Simon, confess I don't really follow that formula...

If hard wired ranges are known and limited then there are a number of alternatives far simpler than my prior suggestion.

My prior suggestion was based on

a) OP modifying the non-contiguous areas at whim
(albeit within contiguous range modification possibly being required)

b) there being no pattern to the non-contiguous ranges forming the defined named range

If there's a pattern then things are simplified also.

[Chuck64]

I have 200 products divided into roughly 12 groups. The total sales for the items reside in column G. So rows 1 -15 are group A and cell G16 sums the total of group A. Group B falls in rows 18 - 30, with the total in row 31. I can find the max value but I can not get the Cell address for it.

Column
A B C D E F G

Group A
Red 10
Blue 12
Green 14
Yellow 20 This my max value. I need the address for cell containing "Yellow"
Total Group A 56

Group B
Apples 15
Oranges 18
Cantelopes 17
Total Group B 50

[Simon Lloyd]

Why not add a workbook?
Providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (it can be dummy data but must be of the same type) and your structure it is far easier for us to give you a tailored, workable answer to your query \1

To upload a workbook, click reply then add your few words, scroll down past the submit button and you will see the Manage Attachments button, this is where you get to add files for upload