Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Question | Excel Discussion (Misc queries) | |||
Range Question | Excel Discussion (Misc queries) | |||
Odd excel usage question: | Excel Worksheet Functions | |||
Range question in '97 | Excel Discussion (Misc queries) | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) |