Max(If(values in this range=X, Maxvalues of other range))

Posted by Roger on March 18, 2001 7:48 PM

I am very new to Excel and it's GREAT! I love the CSE or "array formulas". I can't seem to get a Max(If... formula to work. I want to find the maximum value in a range of cells based on a string in another cell. For example if a value in A1:A2000 = "TPO" of the TPO's found I want the MAX value found in B2:B2000
Can anyone help - It will be much appreciated - Thanks.
I used to think 123 was the best - I'm quickly changing my mind.

Posted by Dave Hawley on March 18, 2001 7:57 PM

Hi Roger

Try this array:

=MAX(IF(A1:A2000="TPO",B1:B2000))

If you follow my link to my we page and then click the link "Array Formulas" you will see some array examples and some important rules regarding array formulas.

Personally I believe the database formulas should always be used first and only use an array if they don't suit. Take a look at the DMAX formula, if you need any help with it let me know.

OzGrid Business Applications

Posted by Mark W. on March 19, 2001 5:53 AM

Even simpler...

{=MAX((A1:A2000="TPO")*B1:B2000)}

Posted by Mark W. on March 19, 2001 8:12 AM

But...

I'd refrain from using this formulation if
the domain of B1:B2000 includes negative numbers.

Posted by Roger on March 19, 2001 11:36 AM

Now I get a #VALUE! error

The {=MAX((A1:A2000="TPO")*B1:B2000)} returns a #VALUE! error. I have tried changing the A range to a numeric data type like the B range is but it doesn't make any difference. Sorry for being so clueless but I would appreciate more help on this matter - many many thanks to you.

Posted by Aladin Akyurek on March 19, 2001 11:48 AM

Re: But...

Why? Negative numbers should be ok. If this range contains any text value, you'll get #VALUE! error. Is it not?

Posted by Mark W. on March 19, 2001 12:18 PM

Re: But...

> Why? Negative numbers should be ok.

If all of the values in B1:B2000 with a
corresponding "TPO" in column A are negative
then {=MAX((A1:A2000="TPO")*B1:B2000)} would
return 0 instead of the negative value nearest
to 0.

Posted by Mark W. on March 19, 2001 12:21 PM

Re: Now I get a #VALUE! error

Roger, before you change any of your values let
me remind you that this is an array formula which
must be entered using the Shift+Ctrl+Enter key
combination. In fact, the braces, {}, are not
to be entered by you. They're supplied by
Excel to signal that this formula has been
accepted as an array formula.

Posted by Mark W. on March 19, 2001 12:34 PM

Re: Now I get a #VALUE! error

Roger, I recommend that you take a look at the
Help topic for "Troubleshoot formulas and error
values" You'll probably need to consult this
reference from time to time so it might be wise
to bookmark it (see the Options menu).

If you'll look at the entry for #VALUE! you'll
notice that the 2nd possibility concerns array
formulas. Knowing that this error can be
associated with an improperly entered array
formula is very helpful in diagnosing your
problem.

Posted by Mark W. on March 19, 2001 12:44 PM

Re: But...

> If this range contains any text value, you'll get #VALUE! error. Is it not?

Yes, but keep in mind that the MAX() function requires numeric
values as arguments too. So if there are text values we
have bigger problems. I'm also relying on belief that most
people don't think in terms of maximum values when considering
text. Which is bigger "cat" or "dog"? Before you say, "dog",
you'd better consider that the "cat" may be a Bengal tiger! ; )

Posted by Roger on March 19, 2001 3:02 PM

Yes I know of the CSE and data types - more help please

Thanks - Yes I am aware that I have to CSE the formula - I have used this type of formula 7 or 8 times for this project so far. I am also aware of data types. That is why I added a column and punched in a few numbers to test the formula but I still got the #VALUE! error. Question - My range does contain several blank cells. This could be my problem. If so... what then? Any way around it? - Thanks again.

Posted by Mark W. on March 19, 2001 3:49 PM

Re: Yes I know of the CSE and data types - more help please

Roger, did you add the new column after you had entered
the array formula? If so, the formula wouldn't have
seen the new column. Select the cell containing the
formula, click on the formula bar and check to see if
the new test column is referenced by the array formula.

Blank cells are okay, but I'm wondering if some of the
cells in column B contain either spaces or zero length
text strings ("").

Do this:

1. Select column B
2. Choose the Data Text to Columns... menu command
3. At Step 1 of 3 select the "Fixed width" data
type option and the press the Finish button.

Posted by Roger on March 19, 2001 8:05 PM

Success

I tried this again at home and it worked just fine. The condition was a string and the Max I was looking for a Numeric. No #VALUE! error. I must have overlooked some simple thing. I just hope it works tomorrow at work.
Thanks to all for the help - :)

Posted by Aladin Akyurek on March 19, 2001 10:49 PM

Roger:

You have 2 formulas to go on:

1. =MAX(IF(A1:A2000="TPO",B1:B2000))
2. =MAX((A1:A2000="TPO")*B1:B2000)

Both are CSE-formulas.
The second requires that B1:B2000 must be free of text values including spaces and error values, & it will not give a correct result in case B1:B2000 contains nothing but negative numbers.
The first is robust in face of such unexpected cases.

Aladin