Calculating Maximums

  • Thread starter Thread starter L Buchy
  • Start date Start date
L

L Buchy

I have 2 columns in a spreadsheet. Column 1 has values of (A, B or C),
Column 2 has numeric values. In one cell I want to calculate the maximum
value for all of rows where column 1 = "A".

Example:

column 1 column2
A 10 A Max = 15
A 5 B Max = 9.25
B 3 C Max = 3
C 2.5
B 9.25
C 3
A 15

How can I calculate this?
 
You could use a formula like:

=IF(A1:A7="A",MAX(B1:B7))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Another way when your list of values in column A gets larger is to do a pivot
table. (Data|pivottable)

There's an option to show Maximums.
 
=Max((A1:A50="A")*B1:B50)

entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

or assume the A is in C1

=Max(($A$1:$A$50=C1)*$B$1:$B$50)
 
What I may have "meant" was:

=MAX(IF(A1:A7="A",B1:B7))
Still array entered. (ctrl-shift-enter)

(all the letters were there, but not in the right order! Oops.)
 
Back
Top