Looking up the highest value

  • Thread starter Thread starter aresar
  • Start date Start date
A

aresar

I have data in columns A and B (B data references the A column).

The numbers in A are not in order and cannot be placed in order.
want to be able to have a few functions that will the return th
highest value, lowest value, and average of the matching data fro
column B if the numbers in A are exactly the same.

Example

A B

10 200
14 10
15 684
10 11
39 906

If I wanted the highest and lowest and average for 10?

Please take into consideration that my list is very long and there wil
be many instances where the value in A is 10.

Any help would be appreciated
 
Consider using pivot tables.

If uou insist on using formulas, create first a list of distinct numbers in
D from D2 on, extracted from column A.

E2:

=MAX(IF($A$2:$A$40=D2,$B$2:$B$40))

F2:

=MIN(IF($A$2:$A$40=D2,$B$2:$B$40))

G2:

=AVERAGE(IF($A$2:$A$40=D2,$B$2:$B$40))

Each of the foregoing formulas must be confirmed with control+shift+enter
instead of just with enter.
 
an added note,

there is data in another column that references the A column I woul
like to do the same with, so the data I want analyzed amy not be in th
very next column
 
Back
Top