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

 

[rthakur]

posted same reply twice, sorry.

 

[DonkeyOte]

rthakur / aladin,

the zero issue stems from my earlier post (when match removed) and the zero should not have been there from that point forth... if removed everything should work (I believe).

 

[Aladin Akyurek]

Greetings Aladin,

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

The formula you have...

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

will return the correct value of 0 (zero), not "0", a text value.

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

No need for the ",0" bit.

=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

The MIN story holds here too... No need for the ",0" bit.

If you don't want #DIV/0!, try...
Control+shift+enter, not just enter...

=LOOKUP(9.99999999999999E+307,
   CHOOSE({1,2},
     0,
     AVERAGE(IF(GNC70901!$B$2:$B$20000="N",
       IF(LEFT(GNC70901!$C$2:$C$20000,2)=B49&"",
         GNC70901!$H$2:$H$20000)))))

 

[rthakur]

Bingo!

That was going to be my next question...how to get rid of #DIV/0 under avergae formula if the value is 0. Thinking ahead and covering all the bases....Aladin you ROCK!

Thank you Aladin & Lasw10, I am grateful for all your kind help

Saved me a lot of time...not to mention accuracy!

...until next riddle

Regards,

rthakur