Help with MAX/If formula

[rthakur]

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

 

[DonkeyOte]

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

 

[rthakur]

Yes, Sir. I did.

 

[DonkeyOte]

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)))

 

[rthakur]

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]

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

 

[DonkeyOte]

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]

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)

 

[Aladin Akyurek]

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]

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