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
|