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

NextWorldAngel

New Member
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 "-"

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
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
I know the formula looks wierd, but Column D is accually the one that I'm finding the max in.

NextWorldAngel

New Member
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

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)))))

NextWorldAngel

New Member
It did, Thank you SOOO Much!!

MrExcel MVP
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``````

Replies
6
Views
148
Replies
3
Views
88
Replies
3
Views
49
Replies
3
Views
28
Replies
0
Views
64