Max IF formula

[willow1985]

Hello,

I am trying to get a Max IF formula to work. At first I thought of using an index match with Max however I read online that the below formula would be a better fit:

=MAX(IF(('% Complete'!A1:A6=A1),'% Complete'!$B$1:$B$7))

I need the formula to return the Maximum percentage from the list below for each color match:

% Complete
A B
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Red [/TD]
[TD="class: xl65, width: 64, align: right"]50%[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD="class: xl65, align: right"]10%[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD="class: xl65, align: right"]90%[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[/TR]
[TR]
[TD]Red [/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD]Red [/TD]
[TD="class: xl65, align: right"]75%[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD="class: xl65, align: right"]25%[/TD]
[/TR]
</tbody>[/TABLE]

This is what the results should be:
A B
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Red [/TD]
[TD="class: xl63, width: 64, align: right"]100%[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD="class: xl63, align: right"]10%[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD="class: xl63, align: right"]90%[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD="class: xl63, align: right"]50%[/TD]
[/TR]
</tbody>[/TABLE]


However what I get is:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Red [/TD]
[TD="class: xl65, width: 64, align: right"]100%[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD="class: xl65, align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated

Thank you

Carla

 

[Fluff]

Re: Need help with a Max IF formula

Did you confirm the formula with Ctrl Shift Enter, rather than just enter?
If so the formula should be wrapped in {}

 

[Fluff]

Re: Need help with a Max IF formula

Another option without the need to use Ctrl Shift Enter is
=AGGREGATE(14,6,'% Complete'!$B$1:$B$7/('% Complete'!$A$1:$A$7=A1),1)

 

[willow1985]

Re: Need help with a Max IF formula

When I confirm with Shift+Ctrl+Enter I still get the incorrect results of:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Red [/TD]
[TD="class: xl65, width: 64, align: right"]100%[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[/TR]
[TR]
[TD]Yellow[/TD]
[TD="class: xl65, align: right"]50%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

Re: Need help with a Max IF formula

Carla

Try this and enter the formula with CTRL+SHIFT+ENTER.

=MAX(IF(('% Complete'!$A$1:$A$7=A1),'% Complete'!$B$1:$B$7))

 

[Fluff]

Re: Need help with a Max IF formula

You need to lock the cell ranges like

Book1
	A	B	C	D	E	F	G	H
1	Red	50%				Red	100%	100%
2	Blue	10%				Blue	10%	10%
3	Green	90%				Green	90%	90%
4	Yellow	50%				Yellow	50%	50%
5	Red	100%
6	Red	75%
7	Yellow	25%
Sheet1

Cell Formulas
Range	Formula
H1	=AGGREGATE(14,6,$B$1:$B$7/($A$1:$A$7=F1),1)
G1	{=MAX(IF($A$1:$A$7=A1,$B$1:$B$7))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

Re: Need help with a Max IF formula

Try a "regular" formula:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Red</td><td style="text-align:right; ">100%</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Blue</td><td style="text-align:right; ">10%</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Green</td><td style="text-align:right; ">90%</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Yellow</td><td style="text-align:right; ">50%</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=SUMPRODUCT(MAX(('% Complete'!$A$1:$A$7=A1)*('% Complete'!$B$1:$B$7)))</td></tr></table></td></tr></table>

 

[willow1985]

Re: Need help with a Max IF formula

I find all the formulas break down when I change the parameters to: '% Complete'!$A$1:$A$1000000=A1

If I want to add more colors for example to match. If there is no match I get a #NUM ! error as well, but I am assuming this is normal? Can I fix this by incorporating an IFERROR function?

Thank you for all your guys help!

Carla

 

[willow1985]

Re: Need help with a Max IF formula

I will share the original book. I was using the Red Blue Green as an example but here is the original book. I removed the sensitive information:

https://www.dropbox.com/s/lfdhj0ciglgmy3b/Tracking.xlsx?dl=0

 

[Fluff]

Re: Need help with a Max IF formula

You're ranges must be the same size like
=IFERROR(AGGREGATE(14,6, 'Labor Input BLANK'!$H$3:$H$1000/('Labor Input BLANK'!$F$3:$F$1000=D2),1),"")

Also do not reference the entire column, it will simply cause Xl to slow right & probably crash.
ALWAYS limit formula ranges to the used range & a LITTLE bit extra to allow for different sized data.