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!!
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!!
That extra 1 was being considered as a number2 in the outer Max formula which I don't think is required.
The existing formula is producing the #Value! error because you cannot multiply a null string with a number. Wrap the internal calculation within IFERROR to handle that like below...
In B2
=MAX(IFERROR((--ISNUMBER(MATCH(data!$C$1:$CF$1,$H$1:$S$1,0)))*INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0),0))In C2
=MAX(IFERROR((--NOT(ISNUMBER(MATCH(data!$C$1:$CF$1,$H$1:$S$1,0))))*INDEX(data!$C$2:$CF$65,MATCH($A2,data!$B$2:$B$65,0),0),0))Both the formulas should be entered as Array Formulas.
Thank you!! That worked perfectly. I think I am having the same issue with the COUNTIF to find average, but I can't figure out where the IFERROR go. Any Advice?
Thanks again
