range usage question
 

I want the maximum of column A when column B contains a certain word. I tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks

range usage question
 

Thanks, but that returns the sum over all the values, not the maximum of the
individual vales.

"pdberger" wrote:

jz --

Here's something that works, but you can't use if for an entire column:

=sumproduct(A1:A100,--(B1:B100="word"))

HTH

"jzingman" wrote:

I want the maximum of column A when column B contains a certain word. I tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks

range usage question
 

jz --

Here's something that works, but you can't use if for an entire column:

=sumproduct(A1:A100,--(B1:B100="word"))

HTH

"jzingman" wrote:

I want the maximum of column A when column B contains a certain word. I tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks

range usage question
 

Try this:

=MAX(INDEX((B1:B100="word")*A1:A100,0))


"jzingman" wrote:

I want the maximum of column A when column B contains a certain word. I tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks

range usage question
 

Try this array formula** :

=MAX(IF(B1:B10="word",A1:A10))

Note that you can't use entire columns as range references unless you're
using Excel 2007.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"jzingman" wrote in message
...
I want the maximum of column A when column B contains a certain word. I
tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks

range usage question
 

=MAX(IF(B1:B1000="word",A1:A1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

You cannot use a whole column in array formulae (prior to excel 2007), but
must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jzingman" wrote in message
...
I want the maximum of column A when column B contains a certain word. I
tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks

range usage question
 

This works. Thanks to all who replied. Doesn't seem especially obvious to
me, but live and learn.

"Bob Phillips" wrote:

=MAX(IF(B1:B1000="word",A1:A1000))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

You cannot use a whole column in array formulae (prior to excel 2007), but
must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jzingman" wrote in message
...
I want the maximum of column A when column B contains a certain word. I
tried
=max(A:A*(B:B="word"))

but get 0. Thinking that the B reference was the problem, I tried

=max(B:B="word")

and get 0, although if I choose a cell that contains word,

=max(B5="word")

I get 1. So clearly I don't know how to do this reference. What's the
right way?

Thanks