Excel – Need formula to find the Highest value among group of entries, if conditions are fullfilled

microsoft excelmicrosoft-excel-2007

I have certain Daily Data on Daily sheet and I need formula Weekly data sheet, suppose 1st week of 2014 starts from 1st Jan 2014, hence on weekly data I need formula which will search that corresponding data in daily sheet and returns the Value in corresponding cell on weekly data sheet. I hope I have make understood my question properly

enter image description here
enter image description here
enter image description here

Best Answer

  • In Excel 2007, your best bet for MAXIFS and MINIFS functionality is to use SUMPRODUCT.

    For your "high price" formula in cell C3, use:

        =SUMPRODUCT(MAX('Daily Data'!$C$2:$C$37*
    (('Daily Data'!$A$2:$A$37>=$A3)*('Daily Data'!$A$2:$A$37<$A3+7))))

    and for your "low price" formula in cell D3, use:

        =SUMPRODUCT(MIN('Daily Data'!$D$2:$D$37*
    (('Daily Data'!$A$2:$A$37>=$A3)*('Daily Data'!$A$2:$A$37<$A3+7)*-10000+10001))`)

    The way the SUMPRODUCT formula works is explained here: https://support.office.com/en-gb/article/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e

    The first function (For high price), works by going row by row down the whole list taking these two conditions ">=$A3" and "

    The min function works similarly, however, we can't use 0s because MIN would return the 0. So instead, we take the true (1) and multiply it by -10000. Then we add 10001. So now all the ones out of the specified date range are 10000x too large, and all the ones inside the date range are at regular scale. So when we take the MIN the ones outside of the date range are way too large to have any chance of being the low price.

  • Related Question