Combing AVERAGEIFS and a criteria function that excludes duplicates

[acillatem]

Hi all - a tricky one here that I hope will challenge a few!

Spreadsheet attached.

What I need to do with this one is combine an AverageIFs function with one that find duplicate values in Column E.

I have an average if function that works fine:

=IFERROR(AVERAGEIFS($Z$2:$Z$19,$N$2:$N$19,">="&$B28,$N$2:$N$19,"<="&$C28,$B$2:$B$19,"="&F$22)," ")

However this one is calculating averages for ALL activities (z2:z19) and not considering the duplicate values in column E

I have worked out that there are actually 13 activities, not 18, through using the following:

=SUM(IF(FREQUENCY(MATCH(E2:E19,E2:E19,0),MATCH(E2:E19,E2:E19,0))>0,1))

What I need then is for my AVERAGEIFS function to understand the FREQUENCY/MATCH command – ie to average only the 13 activities, not the 18 which are duplicates.

Any ideas??

thanks all =D!!!!!

[XOR LX]

But for those 5 pairs of Donor Project IDs which are duplicated you don't say which of each pair you want to keep in the average and which to reject (each pair of duplicates has a different Amount in column Z).

Regards

[acillatem]

Hi XOX LX - thank you you are right. I don't know how to select the right one. will have another think about it.

We manually did the following to try and work it out with another spreadsheet:

Let’s say that the duplicates result in over-counting of the number of activities by 100%, so that instead of counting 100 unique activities we count 200 (so there are 100 duplicate entries). So this means that the denominator in our calculations of the average value of activities is 100, but we used 200, meaning that our average is exactly half what it should be. To get the correct average, we just multiply the original average by the ratio of the total recorded activities (including duplicates) to the unique activities, which in this case is 200/100.

SO I think I need to calculate the number of real and 'fake' activities and manually calculate ratio for the real average. I've already got real and fake numbers (see other post http://www.excelforum.com/excel-form...t-a-total.html).

I will keep trying..!