software

Hi People,

I have two columns of data, I want to find the maximum value in column
B of rows whose corresponding row cell in column A contains 5.4. The
formula I have tried to use is:

=MAX(IF(A2:A101=5.4,B2:B101))

But this doesn't work. Any other ideas?

Can I define a name that will relate to all cells in column B whose
corresponding row cell in column A has a value of 5.4?--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: www.excelforum.com/member.php...oamp;userid=31214
View this thread: www.excelforum.com/showthread...hreadid=521059Did you enter it as an array formula by pressing Ctrl-Shift-Enterquot;coa01gsbquot; wrote:

gt;
gt; Hi People,
gt;
gt; I have two columns of data, I want to find the maximum value in column
gt; B of rows whose corresponding row cell in column A contains 5.4. The
gt; formula I have tried to use is:
gt;
gt; =MAX(IF(A2:A101=5.4,B2:B101))
gt;
gt; But this doesn't work. Any other ideas?
gt;
gt; Can I define a name that will relate to all cells in column B whose
gt; corresponding row cell in column A has a value of 5.4?
gt;
gt;
gt; --
gt; coa01gsb
gt; ------------------------------------------------------------------------
gt; coa01gsb's Profile: www.excelforum.com/member.php...oamp;userid=31214
gt; View this thread: www.excelforum.com/showthread...hreadid=521059
gt;
gt;

What quot;doesn't workquot;?

Are you array-entering it as it should be (i.e., with CTRL-SHIFT-ENTER)?In article gt;,
coa01gsb gt;
wrote:

gt; Hi People,
gt;
gt; I have two columns of data, I want to find the maximum value in column
gt; B of rows whose corresponding row cell in column A contains 5.4. The
gt; formula I have tried to use is:
gt;
gt; =MAX(IF(A2:A101=5.4,B2:B101))
gt;
gt; But this doesn't work. Any other ideas?
gt;
gt; Can I define a name that will relate to all cells in column B whose
gt; corresponding row cell in column A has a value of 5.4?


Your formula is correct but you need to commit it with ctrl shift enter

not just enter, because it is an array formula.--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=521059
Cheers guys, absolutely right I was not pressing CRTL-SHIFT_ENTER--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: www.excelforum.com/member.php...oamp;userid=31214
View this thread: www.excelforum.com/showthread...hreadid=521059