AVERAGEIFS to only factor in lowest value instance of duplicates

[maverick84]

Hi, long time user of the forum but first post so please go easy.

I’ve hit a roadblock creating a slightly complex AVERAGEIFS formula.

I basically need to create an AVERAGEIF formula with multiple criteria, the challenge is I have some duplicate items where for these rows I only want to factor in the lowest respective value to the average. Suspect I need to utilise the MIN function but can’t quite seem to get it functioning correctly.

I’ve attached an example sheet to help illustrate what I’m trying to achieve. It is the rank column which I want to capture the average for. A keyword may rank multiple times but I only want to include within the average calculation the lowest value. At present this isn’t factored (keyword column) into the included formulas.

The sheet is a cut down example so the intention of the formula may not be fully obvious but with a much bigger data set in would likely more clear.

Thanks in advance for any help.

[XLent]

given the nature of your data, primarily the fact it is sorted such that the min of any given combination is always listed first, you might get away with the below Array:

Please Login or Register  to view this content.

above would return 3.5, 5, 2, 2 & 7 for the Category 1 results.

you could equally consider using a Pivot (with repeated labels), as an intermediate solution, to isolate the MIN values for each combination - against which you could apply an averageifs

[Olly]

An alternative approach would be to load your table of data to the data model, and create a DAX measure:

Please Login or Register  to view this content.

Now you can use Pivot Tables to report on your data, using this measure.

See attached workbook for worked example.

[maverick84]

Thanks for the responses, was hoping there was a simple solution that I had missed. Short term I'm going to resort to the intermediary pivot table solution while I read up more around DAX measures.