HELP! finding highest values within data

  • Thread starter Thread starter nickturvey
  • Start date Start date
N

nickturvey

hi couple of things:

is there anyway that excel can atomatically detect the highest value
within a data set, and then insert this value into another cell? eg.

213
245
267
211
497

is there anyway to make excel select the 497 and then copy this to
another cell?

also, after identifying this as the highest value, would it be possible
to then automatically select the cell immmediately to the left of "497"
in this example?

thanks, nick x
 
hi couple of things:

is there anyway that excel can atomatically detect the highest value
within a data set, and then insert this value into another cell? eg.

213
245
267
211
497

is there anyway to make excel select the 497 and then copy this to
another cell?
Click to expand...

In "another cell" place the formula =MAX(rng) where rng is your range of cells
containing the numbers.
also, after identifying this as the highest value, would it be possible
to then automatically select the cell immmediately to the left of "497"
in this example?
Click to expand...

You would need to write a VBA Macro event driven macro.


--ron
 
Assume you have in A1:B5 the data below:

A 213
B 245
C 267
D 211
E 497

and you want the max value in col B
& the value of the cell to the immediate left of this max value

Put in C1: =MAX(B:B)
(this returns the max value from col B)

Put in say D1: =OFFSET($B$1,MATCH(C1,B:B,0)-1,-1,1,1)
(this returns 'E' , ie the value to the immediate left of the max value 497)

hth
Max
 
for the highest value in col B
=max(B:B)

for the cell to the left of the found cell
=INDEX(A:B,MATCH(MAX(B:B),B:B),1)

If you don't know the columns you would need a macro or UDF
 
Back
Top