Farshid Civil
Farshid Civil

Reputation: 13

How to Find the Maximum Value in Specific Rows of a Column in Excel

I have an Excel table with multiple columns, including "story" and "shear X". I need to find the maximum value in the "shear X" column, but only for rows where the "story" column contains "story3".

Here is a sample of my data:

Story Shear X story1 10 story2 15 story3 25 story4 20 story3 30 story2 22 story3 35 ... ... I want to write a formula that identifies the rows containing "story3" and then finds the maximum value in the "shear X" column for those rows.

What I've Tried:

I attempted to use the MAX and IF functions, but I am having trouble getting the correct syntax and making sure it works across the entire dataset.

Here's an example of what I tried:

=MAX(IF(A:A="story3", B:B))

However, this formula doesn't seem to work correctly for me.

Question:

How can I write a formula that correctly identifies rows where the "story" column is "story3" and then finds the maximum value in the "shear X" column for those rows?

Additional Information:

My data starts at row 1. "story" values are in column A. "shear X" values are in column B.

Upvotes: 0

Views: 844

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Depending on the age of your Excel,

Office 365 Excel and later use MAXIFS:

=MAXIFS(C:C,A:A,"story3")

Office 2010 and Later us AGGREGATE:

=AGGREGATE(14,7,C1:C100/(A1:A100="story3"),1)

Earlier versions this array formula:

=MAX(IF(A1:A100="story3",C1:C100))

Being an array formula it must be confirmed with Ctrl-Shift-Enter.


If column C is not always the Shear X column and you need to find it we can use INDEX(MATCH()) to return the correct Column to the above formulas:

INDEX(A:H,0,MATCH("shear X",A2:H2,0))

So:

=MAXIFS(INDEX(A:H,0,MATCH("shear X",2:2,0)),A:A,"story3")

=AGGREGATE(14,7,INDEX(1:100,0,MATCH("shear X",2:2,0))/(A1:A100="story3"),1)

=MAX(IF(A1:A100="story3",INDEX(1:100,0,MATCH("shear X",2:2,0))))

Upvotes: 2

Related Questions