A
Alec Kolundzic
I'm looking for a function that will find the maximum
value in a column, then will put that value in a cell
value in a column, then will put that value in a cell
J
JulieD
Hi Alec
in the cell where you want the maximum value displayed type
=MAX(A1:A10)
where A1:A10 is the range that you want to find the maximum of
if it is a whole column then just use
=MAX(A:A)
Cheers
JulieD
in the cell where you want the maximum value displayed type
=MAX(A1:A10)
where A1:A10 is the range that you want to find the maximum of
if it is a whole column then just use
=MAX(A:A)
Cheers
JulieD
C
Charles Maxson
Use the MAX function >>
e.g. =MAX(A:A)
e.g. =MAX(A:A)
J
Jason Morin
Use MAX.
HTH
Jason
Atlanta, GA
HTH
Jason
Atlanta, GA
G
Govind
Hi,
Use
=MAX(A1:A10) assuming the data range is from a1:a10
Regards
Govind
Use
=MAX(A1:A10) assuming the data range is from a1:a10
Regards
Govind
A
Alec Kolundzic
Thanks Julie
How do I then cell as well as the value?
How do I then cell as well as the value?
J
JulieD
Hi Alec
you put the formula in the cell where you want to see the maximum value
if this isn't what you're after please explain in a bit more detail what
your workbook looks like and what you're trying to achieve
Cheers
JulieD
you put the formula in the cell where you want to see the maximum value
if this isn't what you're after please explain in a bit more detail what
your workbook looks like and what you're trying to achieve
Cheers
JulieD
J
JulieD
Hi Alec
we seem to be going in circles ..
in your original post you said that you'ld like to
a) find the maximum value in a column
hence my answer of =MAX(A:A)
where A is the column that you want to find the maximum of
b) place that value (in my understanding "the maximum") in a cell
so then you need to type =MAX(A:A) in the cell where you want to see the
maximum value of column A
so if you wanted the maximum value of column A to be displayed in cell D3,
you type in cell D3
=MAX(A:A)
has this helped? if not, could you tell me the column that you want to find
the maximum of and the cell reference of where this value is to go, or
outline what it is (using cell references) that you're trying to achive if
i've misunderstood your request.
Cheers
JulieD
we seem to be going in circles ..
in your original post you said that you'ld like to
a) find the maximum value in a column
hence my answer of =MAX(A:A)
where A is the column that you want to find the maximum of
b) place that value (in my understanding "the maximum") in a cell
so then you need to type =MAX(A:A) in the cell where you want to see the
maximum value of column A
so if you wanted the maximum value of column A to be displayed in cell D3,
you type in cell D3
=MAX(A:A)
has this helped? if not, could you tell me the column that you want to find
the maximum of and the cell reference of where this value is to go, or
outline what it is (using cell references) that you're trying to achive if
i've misunderstood your request.
Cheers
JulieD
D
Don
Hi Julie,
I think he wants to know what cell the Max is in as well.
I don't know how to get him there, but I'm watching....
Don
I think he wants to know what cell the Max is in as well.
I don't know how to get him there, but I'm watching....
Don
J
JulieD
Hi Don
glad someone's more awake than me
- Alec if this is the case then
=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))
should give it to you
Cheers
JulieD
glad someone's more awake than me
- Alec if this is the case then
=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))
should give it to you
Cheers
JulieD
J
JulieD
Hi Alec
Don picked up on this in his reply and i've responded on how to do this on
that thread - but basically you need to use the following formula
=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))
Let me know how you go
Regards
JulieD
Don picked up on this in his reply and i've responded on how to do this on
that thread - but basically you need to use the following formula
=CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0)))
Let me know how you go
Regards
JulieD
-snip-Alec Kolundzic said:
A
Alec Kolundzic
Thanks Julie,
I'm sorry, my original message should have read, I'm
trying to find the cell address of the maximum value in a
column, the max function works well for finding the
highest value, but I need to know the cell address of the
highest value.
I'm sorry, my original message should have read, I'm
trying to find the cell address of the maximum value in a
column, the max function works well for finding the
highest value, but I need to know the cell address of the
highest value.
G
Govind
Hi,
Thinks he needs to find out the cell where the maximum value occurred as
well.
In case your data is column A, then use this formula
=ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A1))
This will show you the cell where the maximum value occurred.
Regards
Govind.
Thinks he needs to find out the cell where the maximum value occurred as
well.
In case your data is column A, then use this formula
=ADDRESS(MATCH(MAX(A:A),A:A,0),COLUMN(A1))
This will show you the cell where the maximum value occurred.
Regards
Govind.
D
Don
m..m
Told you I was watching.....lol
Thanks for quick response Julie..I've stored this for
future use.
Don
Told you I was watching.....lol
Thanks for quick response Julie..I've stored this for
future use.
Don
A
Alec Kolundzic
Thanks Govind
A
Alec Kolundzic
Thanks Julie
Thats exactly what I want.
Thats exactly what I want.
A
Aladin Akyurek
A different take...
Let column A house the following from A1 on:
Entries
34
23
27
33
34
31
20
12
In B2 enter & copy down:
=RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1
In C1 enter:
=MAX(A2:A9)
In C2 enter: 1 (This manually entered parameter indicates that you want a
Top 1 list)
In C3 enter:
=MAX(IF(INDEX(A2:A9,MATCH(C2,B2:B9,0))=A2:A9,B2:B9))-C2
which you must confirm/commit with control+shift+enter instead of just with
enter.
The latter calculates the number of ties that the Max value might have in
the range of interest.
In D2 enter & copy down:
=IF(ROW()-ROW(D$2)+1<=$C$2+$C$3,CELL("Address",INDEX($A$2:$A$9,MATCH(ROW()-ROW(D$2)+1,$B$2:$B$9,0))),"")
Note that the formula is anchored to the first cell (i.e., D2) it is entered
by the ROW(D$2) bit.
The result list that you get in D consists of:
$A$2
$A$6
Let column A house the following from A1 on:
Entries
34
23
27
33
34
31
20
12
In B2 enter & copy down:
=RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1
In C1 enter:
=MAX(A2:A9)
In C2 enter: 1 (This manually entered parameter indicates that you want a
Top 1 list)
In C3 enter:
=MAX(IF(INDEX(A2:A9,MATCH(C2,B2:B9,0))=A2:A9,B2:B9))-C2
which you must confirm/commit with control+shift+enter instead of just with
enter.
The latter calculates the number of ties that the Max value might have in
the range of interest.
In D2 enter & copy down:
=IF(ROW()-ROW(D$2)+1<=$C$2+$C$3,CELL("Address",INDEX($A$2:$A$9,MATCH(ROW()-ROW(D$2)+1,$B$2:$B$9,0))),"")
Note that the formula is anchored to the first cell (i.e., D2) it is entered
by the ROW(D$2) bit.
The result list that you get in D consists of:
$A$2
$A$6