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$10023)+($H$3:$H $10023),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,i f(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 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$10023)+($H$3:$H $10023),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 Eduardo,
Tried the formula but am getting an error report:

=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,i f(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,i f(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 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$10023)+($H$3:$H $10023),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.

Eduardo probably meant to say:
=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,i f(and(K3:K1002<0,K3:K1002<""),K3:K1002))))

Regards,
Fred


"Struggling in Sheffield"
wrote in message ...
Hi Eduardo,
Tried the formula but am getting an error report:

=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,i f(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,i f(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 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$10023)+($H$3:$H $10023),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.

...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:K10 02<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" wrote in ... Eduardo probably meant to say:=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1 002,if(and(K3:K1002<0,K3:K1002<""),K3:K1002)))) Regards, Fred "Struggling in soft.com wrote in ... Hi Eduardo, Tried the formula but am getting an error report: =IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,i f(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:K100 2)))) "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$10023)+($H$3:$ H$10023),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" wrote:

...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:K10 02<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" wrote in ... Eduardo probably meant to say:=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1 002,if(and(K3:K1002<0,K3:K1002<""),K3:K1002)))) Regards, Fred "Struggling in soft.com wrote in ... Hi Eduardo, Tried the formula but am getting an error report: =IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,i f(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:K100 2)))) "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$10023)+($H$3:$ H$10023),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.

.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Struggling in Sheffield"
wrote in message ...
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" wrote:

...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:K10 02<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" wrote in
... Eduardo probably
meant to
say:=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1 002,if(and(K3:K1002<0,K3:K1002<""),K3:K1002))))
Regards, Fred "Struggling in
soft.com wrote in
... Hi
Eduardo, Tried the formula but am getting an error report:
=IF(K1019="","",MAX(IF($G$3:$G$1002=$H$3:$H$1002,i f(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:K100 2))))
"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$10023)+($H$3:$ H$10023),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.

.