range usage question

  • Thread starter Thread starter jzingman
  • Start date Start date
J

jzingman

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
 
Thanks, but that returns the sum over all the values, not the maximum of the
individual vales.
 
jz --

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

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

HTH
 
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)
 
=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)
 
This works. Thanks to all who replied. Doesn't seem especially obvious to
me, but live and learn.
 
Back
Top