Multiple Maximums

Skebo · Mar 11th, 2002, 04:48 PM

How do I return cell references for multiple maximums within a range?

ABCDEF
125521

Would like this to return, C1 D1

Mark W. · Mar 11th, 2002, 04:57 PM

Define "multiple maximums".

Ian Mac · Mar 11th, 2002, 05:01 PM

Quote:

On 2002-03-11 15:57, Mark W. wrote:
Define "multiple maximums".

Two fives in this case I assume, the question seems quite clear!

_________________
Kind Regards
Ian Mac

[ This Message was edited by: Ian Mac on 2002-03-11 16:02 ]

Mark W. · Mar 11th, 2002, 05:05 PM

Quote:

Two fives in this case I assume, the question seems quite clear!

Perhaps you are psychic .

Is adjacency a requirement?

Skebo · Mar 11th, 2002, 05:11 PM

Quote:

On 2002-03-11 16:05, Mark W. wrote:

Quote:

Two fives in this case I assume, the question seems quite clear!

Perhaps you are psychic .

Is adjacency a requirement?

No adjacency is not a requirement. Just return references for all values that are tied for the maximum value within the range. No psychic abilities required.

Juan Pablo González · Mar 11th, 2002, 05:16 PM

I was going to think a way of doing it with "simple" formulas, but, then, decided to do it the "easy" way. Try with this UDF



Function ReturnMaxs(Rng As Range) As String

Dim Mx As Double

Dim Cll As Range



If Rng.Count = 1 Then ReturnMaxs = Rng.Address(False, False): Exit Function



Mx = Application.Max(Rng)

For Each Cll In Rng

    If Cll = Mx Then

        If Len(ReturnMaxs) = 0 Then

            ReturnMaxs = Cll.Address(False, False)

        Else

            ReturnMaxs = ReturnMaxs & " " & Cll.Address(False, False)

        End If

    End If

Next Cll



End Function

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting

[ This Message was edited by: Juan Pablo G. on 2002-03-11 16:16 ]

Mark W. · Mar 11th, 2002, 05:21 PM

If your values are on row 1:1 enter the formula...

=IF(A1=MAX($A$1:$F$1),ADDRESS(ROW(A1),COLUMN(A1),4),"")

...into cell A2 and fill right to cell F2.

[ This Message was edited by: Mark W. on 2002-03-11 16:22 ]

Ian Mac · Mar 11th, 2002, 05:35 PM

Do you need the result in the same cell (hope not), otherwise where do you need the result displayed?

Mark W. · Mar 11th, 2002, 05:55 PM

Quote:

On 2002-03-11 16:21, Mark W. wrote:
If your values are on row 1:1 enter the formula...

=IF(A1=MAX($A$1:$F$1),ADDRESS(ROW(A1),COLUMN(A1),4),"")

...into cell A2 and fill right to cell F2.

[ This Message was edited by: Mark W. on 2002-03-11 16:22 ]

If you need the results in a single cell modify my formula above to...

=IF(C1=MAX($A$1:$F$1),ADDRESS(ROW(C1),COLUMN(C1),4)&" ","")

...and then include the formula...

=A2&B2&C2&D2&E2&F2

...for your final results.

Mar 11th, 2002, 06:05 PM

Very nice Mark, I tried your formula and pressed the old F9 and got '={"C1 "}', bizarre but nice! I have a different solution to this problem that gives all the addresses in cells next to each other using a few formulas to return the cells ref's in a constant line, i.e. in A2 A3 A4 etc.
I can't remember it off the top of my head, it's at work??!!
proving you ARE the master, BUT I am the mind reader

,

if my IDEA!!?? is worth it, I'm back at work Wednesday. yours and.........

Mar 11th, 2002, 05:16 PM	#6
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	I was going to think a way of doing it with "simple" formulas, but, then, decided to do it the "easy" way. Try with this UDF Function ReturnMaxs(Rng As Range) As String Dim Mx As Double Dim Cll As Range If Rng.Count = 1 Then ReturnMaxs = Rng.Address(False, False): Exit Function Mx = Application.Max(Rng) For Each Cll In Rng If Cll = Mx Then If Len(ReturnMaxs) = 0 Then ReturnMaxs = Cll.Address(False, False) Else ReturnMaxs = ReturnMaxs & " " & Cll.Address(False, False) End If End If Next Cll End Function _________________ Regards, Juan Pablo G. MrExcel.com Consulting [ This Message was edited by: Juan Pablo G. on 2002-03-11 16:16 ]

Mar 11th, 2002, 05:35 PM	#8
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	Do you need the result in the same cell (hope not), otherwise where do you need the result displayed? __________________ "Have a good time......all the time" Ian Mac

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 04:48 PM	#1
Skebo New Member Join Date: Feb 2002 Posts: 20	How do I return cell references for multiple maximums within a range? ABCDEF 125521 Would like this to return, C1 D1

Mar 11th, 2002, 04:57 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Define "multiple maximums".

Mar 11th, 2002, 05:21 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	If your values are on row 1:1 enter the formula... =IF(A1=MAX($A$1:$F$1),ADDRESS(ROW(A1),COLUMN(A1),4),"") ...into cell A2 and fill right to cell F2. [ This Message was edited by: Mark W. on 2002-03-11 16:22 ]

Mar 11th, 2002, 06:05 PM	#10
Guest Posts: n/a	Very nice Mark, I tried your formula and pressed the old F9 and got '={"C1 "}', bizarre but nice! I have a different solution to this problem that gives all the addresses in cells next to each other using a few formulas to return the cells ref's in a constant line, i.e. in A2 A3 A4 etc. I can't remember it off the top of my head, it's at work??!! proving you ARE the master, BUT I am the mind reader , if my IDEA!!?? is worth it, I'm back at work Wednesday. yours and.........