Max IF formula

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
633
Office Version
  1. 2019
Platform
  1. Windows
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
Red 50%
Blue10%
Green90%
Yellow50%
Red 100%
Red 75%
Yellow25%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

This is what the results should be:
A B
Red 100%
Blue10%
Green90%
Yellow50%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>


However what I get is:

Red 100%
Blue100%
Green100%
Yellow100%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>


Any help would be greatly appreciated

Thank you :)

Carla
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 24, 2019
Messages
633
Office Version
  1. 2019
Platform
  1. Windows
Re: Need help with a Max IF formula

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

Red 100%
Blue50%
Green50%
Yellow50%

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Re: Need help with a Max IF formula

You need to lock the cell ranges like

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Red</td><td style="text-align: right;;">50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Red</td><td style="text-align: right;;">100%</td><td style="text-align: right;;">100%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Blue</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Blue</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">10%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Green</td><td style="text-align: right;;">90%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Green</td><td style="text-align: right;;">90%</td><td style="text-align: right;;">90%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Yellow</td><td style="text-align: right;;">50%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Yellow</td><td style="text-align: right;;">50%</td><td style="text-align: right;;">50%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Red</td><td style="text-align: right;;">100%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Red</td><td style="text-align: right;;">75%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Yellow</td><td style="text-align: right;;">25%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H1</th><td style="text-align:left">=AGGREGATE(<font color="Blue">14,6,$B$1:$B$7/(<font color="Red">$A$1:$A$7=F1</font>),1</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G1</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$A$1:$A$7=A1,$B$1:$B$7</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 24, 2019
Messages
633
Office Version
  1. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,928
Messages
5,545,080
Members
410,652
Latest member
Zot
Top