...if(and(K3:K1002<>0,K3:K1002<>"")...

Can't use AND for array comparisons like that. AND returns a scalar where

you need an array. You'd need to write it like this:

=IF(K1019="","",MAX(IF(G3:G1002=H3:H1002,IF(K3:K1002<>0,IF(K3:K1002<>"",K3:K1002)))))All the nested IFs are essentially like saying "and":IF G3:G1002=H3:H1002 *and* K3:K1002<>0 *and* K3:K1002<>""...Whether or not that solves the problem, I'm not sure I understand what isbeing asked so that's all I got!--BiffMicrosoft Excel MVP"Fred Smith" <

[email protected]> wrote in messageEduardo probably meant to say:>=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,if(and(K3:K1002<>0,K3:K1002<>""),K3:K1002))))>> Regards,> Fred>>> "Struggling in Sheffield"<

[email protected]> wrote in messageHi Eduardo,>> Tried the formula but am getting an error report:>>>> =IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,if(and(K3:K1002<>0,>> K3:K1002"" ),K3:K1002))))>>>> K3:K1002"" is flagging up as invalid in the AND function.>>>>>> "Eduardo" wrote:>>>>> Hi,>>> Enter as an array formula>>>>>>=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,if(and(K3:K1002<>0,K3:K1002""),K3:K1002))))>>>>>> "Struggling in Sheffield" wrote:>>>>>> > Hi all,>>> > After some recent help from the forum I'm successfully using thefollowing>>> > array formulas to calculate the MAX values in several columns ofcells. The>>> > values used to calculate the MAX depend on other numerical valueslocated in>>> > adjacent columns:>>> >>>> > {=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,K3:K1002)))}>>> >>>> >{=IF(P1019="","",MAX(IF($E$3:$E$1002=$F$3:$F$1002,IF($G$3:$G$1002=$H$3:$H$1002,P3

1002))))}>>> >>>> > {=IF(U1019="","",MAX(IF($G$3:$G$1002=0,IF($H$3:$H$1002=0,U3:U1002))))}>>> >>>> > {=IF(AL1019="","",MAX(IF($G$3:$G$1002+$H$3:$H$1002<3,AL3:AL1002)))}>>> >>>> >{=IF(AK1019="","",MAX(IF(($G$3:$G$1002>3)+($H$3:$H$1002>3),AK3:AK1002)))}>>> >>>> > I was hoping I could also calculate AVERAGE and MIN values using thesame>>> > basic formulas. However, I have cells within my data ranges which have0>>> > (zero) values (which I use) whilst other cells are unused (blank).>>> >>>> > How can I rework my formulas to extract AVERAGE and MIN values whilst>>> > ignoring the blank cells within my data ranges?>>> >>>> > Cheers.>