Help with MAX/If formula

rthakur

Board Regular
Joined
Feb 22, 2005
Messages
121
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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)))
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0
Greetings Aladin,

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
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top