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.
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))))