CODEWITHSUNDEEP
excelmatchaveragevlookup
crams
crams

Reputation: 325

Excel averageifs by looking up column for criteria

I have a spreadsheet that looks like this: enter image description here

The data is in the coloured section, to the right of that is a table that shows the average of the result column when the data under ABC, XYZ, or GGG is inbetween the Max and Min Value.

This was done with this formula: =AVERAGEIFS($D$4:$D$27,A$4:A$27,"<"&$F5,A$4:A$27,">"&$G5)

What I want to do is have that formula reference the heading and then lookup the appropriate column in the data table to apply the averageif criteria to.

The formula I have now works fine but as I add more columns and the data table that may be out of order it would be much better if I was able to incorporate a lookup or match into the averageifs formula.

Upvotes: 0

Views: 4804

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

The following non-volatile function will do it:

=SUMPRODUCT(($A$1:$C$1=H$1)*($A$2:$C$21>=$G2)*($A$2:$C$21<=$F2)*($A$2:$C$21))/SUMPRODUCT(($A$1:$C$1=H$1)*($A$2:$C$21>=$G2)*($A$2:$C$21<=$F2))

enter image description here

EDIT

To get the average of the results column when the other column is within the constraints. Change the fourth Criterion in the first SUMPRODUCT():

=SUMPRODUCT(($A$1:$C$1=H$1)*($A$2:$C$21>=$G2)*($A$2:$C$21<=$F2)*($D$2:$D$21))/SUMPRODUCT(($A$1:$C$1=H$1)*($A$2:$C$21>=$G2)*($A$2:$C$21<=$F2))

enter image description here

EDIT2

Another option that is both non volatile and non array:

=AVERAGEIFS(D:D,INDEX(A:C,0,MATCH(H$1,1:1,0)),">=" & G4,INDEX(A:C,0,MATCH(H$1,1:1,0)),"<=" &F4)

Upvotes: 2

Forward Ed
Forward Ed

Reputation: 9874

if you are doing it over a small number of calcs then the Offset function will work for you replace your reference to your current A column with

OFFSET($A$4,0,MATCH(h$2,$a$2:$d$2,0))-1,24,1)

The offset is broken down in to $A$4 being your reference point. The 0 being how many rows to move from the reference point to start The MATCH(h$2,$a$2:$d$2,0))-1 being how many columns to move from the reference point The 24 is the number of rows to return The 1 is how many columns to return

The match formula looks for the value in H2 in your header road in A2:D2 for an exact match and returns the number for the spot the item was found. We subtract 1 from this result as we only want to move one less spot than where the item was found. So it if was found in the 1st spot we dont want to move over any columns so we need to make the value 0.

You end formula would look something like:

=AVERAGEIFS($D$4:$D$27,OFFSET($A$4,0,MATCH(h$2,$a$2:$d$2,0))-1,24,1),"<"&$F5,OFFSET($A$4,0,MATCH(h$2,$a$2:$d$2,0))-1,24,1),">"&$G5)

Upvotes: 2

Related Questions