Forum Discussion
best way to find max and average values for data that meets certain criteria
E2 had to be:
=SUMPRODUCT(IFERROR((COUNTIF($H$1:$S$1,data!$C$1:$CF$1)=0)*
INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0),0)/
SUMPRODUCT((COUNTIF($H$1:$S$1,data!$C$1:$CF$1)=0)*
(INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0)<>"")))I got it to work!!! Thank you everyone for all of the help!!
As variant
Max If Sale
=AGGREGATE(14,6,1/--ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0))*INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0),1)Max If Not Sale
=AGGREGATE(14,6,1/--NOT(ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0)))*INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0),1)Average if sale
=SUMPRODUCT(ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0))*INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0))/SUMPRODUCT(ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0))*(INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0)<>0))Average if Not Sale
=SUMPRODUCT(NOT(ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0)))*INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0))/SUMPRODUCT(--NOT(ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0)))*(INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0)<>0))I don't know if you know this or not. Why does aggregate work with Large but not max, average or count. I know the difference between Large(,) and Max() bit it doesn't see like that should be applying here.
AGGREGATE() works with array formulas as references starting from function #14 (Large) and next one. Previous functions work with arrays themselves.
I go an try this on my real data and I am getting an error with the Max for both. I get a #VALUE error. I copied a large subset over to test spreadsheet and I am getting the same results. I tried to trouble shoot this and I am not seeing it.
Do you (or anyone else) see what I am missing?
Your methods work. I ended up going:
=MAX(--ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0))*INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0),1)
=MAX(--NOT(ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0)))*INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0),1)
For the max of sales and not sales respectively.
What I do not understand is why if I try to do the same thing for average:
=AVERAGE(--ISNUMBER(MATCH(data!$C$1:$N$1,$M$1:$P$1,0))*INDEX(data!$C$2:$N$13,MATCH($A2,data!$B$2:$B$13,0),0),1)
It doesn't work. Why doesn't it?
I guess with MAX() instead of AGGREGATE() you use array formula which is not needed in latest case.
AVERAGE() doesn't work since using ISNUMBER() you add to the array zeroes for the values where condition is not met (in addition to zero sales) and average with these zeroes. Plus ,1 at the end of the formula is not needed.
So I need an ifzero()function instead and isnumber(). What is the 1 at the end of the Max relating to? The --NOT?