I want to know which cell(s) are the MAX

[Vulpix]

Hi everyone, I`m relatively new to Excel but I think I`ve mastered the
basics enough not to get flamed here.

My question is this, I have a bunch of values, that I want to find the
biggest out of, that is easily done using the MAX function. Now, I also need
excel to tell me the number of the cell in which is the biggest, for
example, say we had 5 cash values $3, $7, $9, $10, $3, and those values were
numbered 1, 2, 3, 4, 5 in a list, I not only want to know that $10 is the
biggest, but also that it is at number 4. Is there any way to do this
please?

 

[Frank Kabel]

Hi
one way:
=MATCH(MAX(A1:A99),A1:A99,0)
return the cell index within your range

HTH
Frank

 

[Ron Rosenfeld]

Hi everyone, I`m relatively new to Excel but I think I`ve mastered the
basics enough not to get flamed here.

My question is this, I have a bunch of values, that I want to find the
biggest out of, that is easily done using the MAX function. Now, I also need
excel to tell me the number of the cell in which is the biggest, for
example, say we had 5 cash values $3, $7, $9, $10, $3, and those values were
numbered 1, 2, 3, 4, 5 in a list, I not only want to know that $10 is the
biggest, but also that it is at number 4. Is there any way to do this
please?

Assuming the values are in a contiguous range, say A1:A5, then:


=MATCH(MAX(A1:A5),A1:A5,0)


--ron

 

[Vulpix]

Thanks thats just what I need, tell me, is there a way to use MATCH
to find the LAST occurance of a value in an array?

 

[Frank Kabel]

Hi Vulplix
one way:
=MAX(IF(A1:A99=2,ROW(A1:A99),0))
returns the row number of the last occurence for '2'. Note: Enter this
formula as array formula (CTRL+SHIFT+ENTER)

HTH

 

[Vulpix]

Thanks once again, last one for today I promise, I want the cell I found
using the MATCH function to be at the bottom of the list, and
the top of the list to be cell 0 in that column, or to put it another way,
my array range would be cell H0 to H whereever the MAX value occured. Any
ideas how I might do that??? I tried pasting the match into the new formula
but could not get it to work.

 

[Frank Kabel]

Hi Vuplix
try
=OFFSET($H$1,0,0,MAX(IF(A1:A99=2,ROW(A1:A99),0)))
also array entered

so for example
=SUM(OFFSET($H$1,0,0,MAX(IF(H1:H99=2,ROW(H1:H99),0))))
will sum all values starting in H1 down to the last occurence of '2'
HTH
Frank

also enter as array formula

 

[Ken Wright]

Another way to find the last value:-

=LOOKUP(9.99999999999999E+307,A:A) for last numeric entry in a column

=LOOKUP(9.99999999999999E+307,1:1) for last numeric entry in a row

=LOOKUP(REPT("z",255),A:A) for last text entry in a column

=LOOKUP(REPT("z",255),1:1) for last text entry in a row