Forum Discussion

Jay Lea's avatar
Jay Lea
Copper Contributor
Dec 10, 2019
Solved

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 avera...
excel
Formulas and Functions
  • Jay Lea's avatar
    Jay Lea
    Dec 12, 2019

    Subodh_Tiwari_sktneer 

     

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

Jay Lea's avatar
Jay Lea
Copper Contributor
Dec 11, 2019

SergeiBaklan 

 

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. 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 11, 2019

Jay Lea 

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

  • Jay Lea's avatar
    Jay Lea
    Copper Contributor
    Dec 11, 2019

    SergeiBaklan 

     

    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? 

Resources