SOLVED

# best way to find max and average values for data that meets certain criteria

Occasional Contributor

# best way to find max and average values for data that meets certain criteria

I have a table with names in column B and dates in row 1. The rest of the tables is the sales amount for the employee listed in column B on the date in row 1.

I want to find the max and the average for certain days. For example we have 12 sales on various days a quarter. I want to be able to find the max sales amount on days days that we had sales, and the days we didn't have sales as well as the averages sales amount on the days we had sales and the days we didn't have sales.

I can find the row that the data is in using match: =MATCH(A2,data!B:B,0) I can find the if is a sale using max and maxifs in an arrary: {=MAX(MAXIFS(data!\$C2:\$N2,data!\$C\$1:\$N\$1,report!\$M\$1:\$P\$1))} or using =AGGREGATE(14,4,MAXIFS(data!\$C2:\$N2,data!\$C\$1:\$N\$1,report!\$M\$1:\$P\$1),1)

There is a problem with finding the max if it is not a sale. I am not sure why. I tried: =AGGREGATE(14,4,MAXIFS(data!\$C2:\$N2,data!\$C\$1:\$N\$1,"<>"&report!\$M\$1:\$P\$1),1) & {=MAX(MAXIFS(data!\$C2:\$N2,data!\$C\$1:\$N\$1,"<>"&report!\$M\$1:\$P\$1))}

I can find the average if it is a sale if there is data for every day, but not if there is data missing. Then I get a #DIV/0 error.

Any ideas on this?

20 Replies

# Re: best way to find max and average values for data that meets certain criteria

You may try this...

In C2

``=MAX(MAXIFS(INDEX(data!\$C\$2:\$N\$13,MATCH(\$A2,data!\$B\$2:\$B\$13,0),),data!\$C\$1:\$N\$1,report!\$M\$1:\$P\$1))``

In D2

``=MAX(MAXIFS(INDEX(data!\$C\$2:\$N\$13,MATCH(\$A2,data!\$B\$2:\$B\$13,0),),data!\$C\$1:\$N\$1,"<>"&report!\$M\$1:\$P\$1))``

# Re: best way to find max and average values for data that meets certain criteria

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))``

# Re: best way to find max and average values for data that meets certain criteria

If your definition of best is "the shortest", I will recommend this formula in C2, as shown in the snapshot below:

Delving into the attached file, you'll see this formula in G2:

=SUMPRODUCT(COUNTIF(\$M\$1:\$P\$1,data!\$C\$1:\$N\$1)*
INDEX(data!\$C\$2:\$N\$13,MATCH(\$A2,data!\$B\$2:\$B\$13,0),0))/
SUMPRODUCT(COUNTIF(\$M\$1:\$P\$1,data!\$C\$1:\$N\$1*
(INDEX(data!\$C\$2:\$N\$13,MATCH(\$A2,data!\$B\$2:\$B\$13,0),0)<>"")))

Note that I used the elegant COUNTIF instead of the familiar ISNUMBER-MATCH combination.

# Re: best way to find max and average values for data that meets certain criteria

The one for C2 worked perfectly, but the one for D2 did not. The "<>"& doesn't give an error but it doesn't seem to be working.   You are doing it they way I wanted to do it. I wasn't sure how to do an index match with maxifs, but it is working.  Why does the <> work with an array?

Anyone know the answer to this?

# Re: best way to find max and average values for data that meets certain criteria

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?

# Re: best way to find max and average values for data that meets certain criteria

This does work. It seems there should be a shorter way to do it. Is the issue dealing with Arrays or why doesn't the Average or averageif work here?

# Re: best way to find max and average values for data that meets certain criteria

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.

# Re: best way to find max and average values for data that meets certain criteria

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.

# Re: best way to find max and average values for data that meets certain criteria

AGGREGATE() works with array formulas  as references starting from function #14 (Large) and next one. Previous functions work with arrays themselves.

# Re: best way to find max and average values for data that meets certain criteria

So I need an ifzero()function instead and isnumber().  What is the 1 at the end of the Max relating to? The --NOT?

# Re: best way to find max and average values for data that meets certain criteria

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?

# Re: best way to find max and average values for data that meets certain criteria

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.

# Re: best way to find max and average values for data that meets certain criteria

By admitting that my recommended formulas return your expected results, you must be contented thereby. Nonetheless, I admire your quest for a shorter formula but I deem my recommendation as heretofore the shortest!
The group of AVERAGE/IF/S cannot process array formulas! The most powerful among their “relatives” is the elegant COUNTIF, as you can prove in the file I attached earlier.

# Re: best way to find max and average values for data that meets certain criteria

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!!

# Re: best way to find max and average values for data that meets certain criteria

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.

# Re: best way to find max and average values for data that meets certain criteria

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

# Re: best way to find max and average values for data that meets certain criteria

Like this...

In D2

``````=SUMPRODUCT(IFERROR((COUNTIF(\$G\$1:\$S\$1,data!\$C\$1:\$CF\$1)*
INDEX(data!\$C\$2:\$CF\$65,MATCH(\$A2,data!\$B\$2:\$B\$65,0),0))/
SUMPRODUCT(COUNTIF(\$G\$1:\$S\$1,data!\$C\$1:\$CF\$1*
(INDEX(data!\$C\$2:\$CF\$65,MATCH(\$A2,data!\$B\$2:\$B\$65,0),0)<>""))),0))``````

In E2

``````=SUMPRODUCT(IFERROR((COUNTIF(\$G\$1:\$S\$1,data!\$C\$1:\$CF\$1)*
INDEX(data!\$C\$2:\$CF\$65,MATCH(\$A2,data!\$B\$2:\$B\$65,0),0))/
SUMPRODUCT(COUNTIF(\$G\$1:\$S\$1,data!\$C\$1:\$CF\$1*
(INDEX(data!\$C\$2:\$CF\$65,MATCH(\$A2,data!\$B\$2:\$B\$65,0),0)<>""))),0))``````
Best Response confirmed by Jay Lea (Occasional Contributor)
Solution

# Re: best way to find max and average values for data that meets certain criteria

=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!!

# Re: best way to find max and average values for data that meets certain criteria

I’m glad you did! Nonetheless, I aver that my recommended formulas were crucial. Clearly, what you claim to have done actually came from my recommended formulas! I’m just sad you didn’t accord me any recognition for that. Worse, you simply ignored my explanation to your queries. Hopefully, you realize your sheer inadvertence, honest mistake, or excusable negligence, any of which I hereby expressly condone.