Excel: AverageIFS using LOOKUP or SUMPRODUCT instead

2
votes

I have a spreadsheet with a lot of data columns and a result column beside all of the data. I am trying to create a formula that will give me the average of the results column if the data in the adjacent columns falls within a specified range.

I have this working when I want to put a range on one column of data but am having trouble now with putting the range on multiple columns of data.

My spread sheet looks like this:

enter image description here

In another sheet is all the data, Basically I am trying to get the average of the results when the $FTSEMIB data is between 50 and 0.19 AND the Bank data is between 50 and -15.

I was able to do this by using this averageif: 

=AVERAGEIFS(Data!$BHV$10:$BHV$119,Data!$HQ$10:$HQ$119,"<"&G25,Data!$HQ$10:$HQ$119,">"&H25,Data!$TK$10:$TK$119,"<"&G26,Data!$TK$10:$TK$119,">"&H26)

But I realy want to tweak this by using a Lookup or Sumproduct function, that looks up the heading in F25 and F26 then applies the range to them, then averages the adjacent results only if it fits into both ranges.

I tried to do this with this array formula which I had from before when there was only one category to apply the range to but now I am having trouble adding multiple categories

=IFERROR(AVERAGE(IF((Data!$HQ$7:$LZ$7=F$25)*(Data!$HQ$11:$LZ$119>=$H25)*(Data!$HQ$11:$LZ$119<=$G25)*(Data!$BHV$11:$BHV$119<>"")*(Data!$QK$7:$UT$7=F$26)*(Data!$QK$11:$UT$119>=$H26)*(Data!$QK$11:$UT$119<=$G26),Data!$BHV$11:$BHV$119)),"")
1
excelaveragevlookup
In your formula you are testing for nulls(Data!$BHV$11:$BHV$119<>""), are those nulls via a formula or truly empty cells with no formula or spaces? - Scott Craner
The nulls come from a formula, caught by an if error that returns "". I could adjust this though so the iferror returns a number 1000 for example instead of a blank, that way it would be to high to be included in any range - crams

1 Answers

2
votes

This screenshot shows my attempt to replicate your setup:

enter image description here

The formula in column D is based on the following steps:

  1. Find the column number corresponding to the category

    MATCH(A2,F1:H1,0)

  2. Return the data in the column

    INDEX(F2:H5,0,MATCH(A2,F1:H1,0))

  3. Calculate the Average of Result based on the returned column

    AVERAGEIFS($I$2:$I$5,INDEX($F$2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),"<"&B2,INDEX($F$2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),">"&C2)

  4. Now for two columns

    AVERAGEIFS($I$2:$I$5,INDEX($F$2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),"<"&B2,INDEX($F$2:$H$5,0,MATCH(A2,$F$1:$H$1,0)),">"&C2, INDEX($F$2:$H$5,0,MATCH(A3,$F$1:$H$1,0)),"<"&B3,INDEX($F$2:$H$5,0,MATCH(A3,$F$1:$H$1,0)),">"&C3)