# Help with MAX/If formula

#### rthakur

##### Board Regular
Greetings all!

Original working formula:

=MAX(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(ISNUMBER(MATCH(LEFT(GNC70901!\$C\$2:\$C\$20000,2),\$B\$49:\$B\$64&"",0)),GNC70901!\$H\$2:\$H\$20000)))

Instead of a range (highlighted) I only want it to look at a specific cell but when I made that change (below formula) it stopped working...what am I not doing right? What am I missing in the below formula? It is not calculating....returns the value of "0".

=MAX(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(ISNUMBER(MATCH(LEFT(GNC70901!\$C\$2:\$C\$20000,2),B49&"",0)),GNC70901!\$H\$2:\$H\$20000)))

Thank you!

Regards,
rthakur

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### DonkeyOte

##### MrExcel MVP
did you reaffirm the array once you altered it (CTRL + SHIFT + ENTER)

Yes, Sir. I did.

#### DonkeyOte

##### MrExcel MVP
Well it will return 0 if it can't find a match of Left(Cx,2) in B49 and in theory you could I think *simplify* (?) this to:

=MAX(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000,0)))

Last edited:

#### rthakur

##### Board Regular
Hi there!

=MAX(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000,0)))

Above formula is working like a charm! Thank you so much

Also, may I ask you another favor...or i should say favors...

Problem# 1
Above formula calculates the biggest number. How can above formula be modified to calculate smallest number with exact same variables? I tried replacing "MAX" with "MIN" but it did not work...

Problem # 2
I want to calculate the average as well, using above scenario i.e. Average (IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000,0)))

Thanks again, this has saved me a lot of time...

regards,

rthakur

#### rthakur

##### Board Regular
Biggest number formula:
=MAX(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000)))

Smallest number formula:
=MIN(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000)))

I deleted the ",0" at the end of the formula and both are still working fine. Not sure why I needed the ",0" in the end. Even though the result matches the manula calculations, I hope by deleted ",0" I did not made any error...

Problem # 3 still outstanding...

regards,
rthakur

Last edited:

#### DonkeyOte

##### MrExcel MVP
Re: MIN (& MAX for that matter) I don't think you need either the 9999 nor the 0 -- this was my bad from an earlier post where I stripped out the Match.

=MIN(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000)))

#### DonkeyOte

##### MrExcel MVP
For your average calc I'd suggest going with a SUMPRODUCT

=SUMPRODUCT(--(GNC70901!\$B\$2:\$B\$20000="N"),--(LEFT(GNC70901!\$C\$2:\$C20000,2)=B49&""),--(GNC70901!\$H\$2:\$H20000))
\
SUMPRODUCT(--(GNC70901!\$B\$2:\$B\$20000="N"),--(LEFT(GNC70901!\$C\$2:\$C20000,2)=B49&""))

top one gives you your numerator (sum of) and bottom part gives you denominator (count of)

##### MrExcel MVP
Biggest number formula:
=MAX(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000)))

Smallest number formula:
=MIN(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000)))

I deleted the ",0" at the end of the formula and both are still working fine. Not sure why I needed the ",0" in the end. Even though the result matches the manula calculations, I hope by deleted ",0" I did not made any error...

How do you mean?

If no N in column and/or equality to B49& not hold, then return what?

Problem # 3 still outstanding...

regards,
rthakur

You mean the AVERAGE calc. What result did you get with the one you tried?

#### rthakur

##### Board Regular

If no N in column and/or equality to B49& not hold, then return what? Return "0"

But when I left ",0" within the MIN formula, it would "0" value under all cells...

=AVERAGE(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000,0))) returns incorrect value. Mostly 1 and 0

=AVERAGE(IF(GNC70901!\$B\$2:\$B\$20000="N",IF(LEFT(GNC70901!\$C\$2:\$C\$20000,2)=B49&"",GNC70901!\$H\$2:\$H\$20000))) returns correct value (checked with manual calculation.

reagrds,

rthakur

Replies
3
Views
240
Replies
4
Views
223
Replies
3
Views
2K
Replies
3
Views
484
Replies
1
Views
493

1,191,417
Messages
5,986,446
Members
440,030
Latest member
ninomato

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back