=max(if(a2:a101=5.4,b2:b101))?

[coa01gsb]

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?

[Duke Carey]

Did you enter it as an array formula by pressing Ctrl-Shift-Enter


"coa01gsb" wrote:

>
> 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: http://www.excelforum.com/member.php...o&userid=31214
> View this thread: http://www.excelforum.com/showthread...hreadid=521059
>
>

[mphell0]

Your formula is correct but you need to commit it with ctrl+shift+enter
not just enter, because it is an array formula.

[JE McGimpsey]

What "doesn't work"?

Are you array-entering it as it should be (i.e., with CTRL-SHIFT-ENTER)?


In article <[email protected]>,
coa01gsb <[email protected]>
wrote:

> 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]

Cheers guys, absolutely right I was not pressing CRTL-SHIFT_ENTER