MAX AVERAGE MIN with additional criteria, ignoring blank cells

  • Thread starter Thread starter Struggling in Sheffield
  • Start date Start date
S

Struggling in Sheffield

Hi all,
After some recent help from the forum I'm successfully using the following
array formulas to calculate the MAX values in several columns of cells. The
values used to calculate the MAX depend on other numerical values located 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:P1002))))}

{=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 the same
basic formulas. However, I have cells within my data ranges which have 0
(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.
 
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))))
 
Hi 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 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
 
...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:P1002))))}>>> >>>> > {=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.>
 
Well done Biff, don't know what's happening and you still come up trumps! Top
man.

Formula works fine for MAX AVERAGE & MIN of a column of numbers but only
using certain entries (depending on entries in other adjacent columns) and
ignoring blank cells.

Cheers.
Steve,

T. Valko said:
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 messagenews:%[email protected]...> Eduardo 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 messagenews:[email protected]...>> Hi 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,>>>
Click to expand...
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:P1002))))}>>> >>>> > {=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.>
 
Back
Top