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

 

[willow1985]

Re: Need help with a Max IF formula

Thank you so much! I learned a lot today. I think I will use this formula more often. I like that the AGGREGATE ignores errors and that you can chose to have it return the 1st or 2nd Largest value etc.

Thank you very very much!!

Carla

 

[DanteAmor]

Re: Need help with a Max IF formula

Use sumproduct formula:

=SUMPRODUCT(MAX(('Labor Input BLANK'!$F$3:$F$1000=D2)*('Labor Input BLANK'!$H$3:$H$1000)))

Other recommendations. Your current file is 17 megabytes

Do not copy formulas, or validations, or formats throughout the sheet, just copy what you occupy. If you delete all the rows you don't occupy you will have a 57Kb file. This makes it easier to work.

 

[Fluff]

Re: Need help with a Max IF formula

You're welcome & thanks for the feedback

 

[willow1985]

Re: Need help with a Max IF formula

The worksheet is not mine but I will clean it up before I send it back to my college.

Thank you very much for all your help

Carla

 

[DanteAmor]

Totally agree.