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!!
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?
Extra 1 in this your formula
=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)
IFZERO() doesn't exists and in any case doesn't help - you will remove from calculations days with zero sales.
MATCH() returns an array positions where one array with dates met another one, and errors (#N/A) otherwise. ISNUMBER() converts errors to zero and found positions to 1.
Two hopefully final questions.
1) Do I have an extra 1 on the max formulas?
=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)
Or do I need that one for Max? I am confused on the difference as that was working with sample data.
2) I attached a the spreadsheet now with some of my real data. I am not getting a #Value Error. Do you see why?
Thank you for all of your help!!