Add condition to Max formula, If=0, "-"

NextWorldAngel

New Member
Joined
Oct 19, 2006
Messages
31
I am using this array formula
=MAX((A15:A300)*(B15:B300="Local")*(D15:D300=MAX((B15:B300="Local")*(D15:D300))))

I need to add a condition that if there is no information in the range that is looking for the MAX number, then it will come back with a "-"
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Hozz

Board Regular
Joined
Feb 10, 2005
Messages
140
give this a shot...

=IF(MAX(A15:A300)=0,"-",MAX((A15:A300)*(B15:B300="Local")*(D15:D300=MAX((B15:B300="Local")*(D15:D300)))))
 

NextWorldAngel

New Member
Joined
Oct 19, 2006
Messages
31
I know the formula looks wierd, but Column D is accually the one that I'm finding the max in.
 

NextWorldAngel

New Member
Joined
Oct 19, 2006
Messages
31
I'm looking for the Max in Column D
If In Column B the criteria="Local"
And pulling the number in Column A as the answer.
 

Hozz

Board Regular
Joined
Feb 10, 2005
Messages
140

ADVERTISEMENT

Then I think this will work...

=IF(MAX(D15:D300)=0,"-",MAX((A15:A300)*(B15:B300="Local")*(D15:D300=MAX((B15:B300="Local")*(D15:D300)))))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I'm looking for the Max in Column D
If In Column B the criteria="Local"
And pulling the number in Column A as the answer.

Given:

Code:
3	local		4
6	local		7
5	c		6
21	local		7
9	c		8
5	c		2
9	local		7
what is the answer?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,956
Messages
5,545,177
Members
410,668
Latest member
Gaexel
Top