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.

.