averageifs with a criteria that does not equal a cell value

**BobbyS** · 08-13-2012, 03:32 PM

Hi there,

I'm trying to do an averageifs formula that appears for each entry in column T of my spreadsheet. So for the averageifs in cell T2, I need a formula that takes the average of values in column E over observations whose entry in column G matches that of cell R2 and whose entry in column A is different from H2. I can't get the 'does not equal H2' part of the formula to work when I copy the averageifs formula down column T. The code:

=AVERAGEIFS(E:E,G:G, R2,A:A, <>H2 )

doesn't work and I don't know why. Putting inverted commas makes every averageifs check that entries taken into the average do not have a value in a equal to H2, but I want that check to be does not equal Hx for each row x I apply the formula in T to.

Your help would be dearly appreaciated!

**daddylonglegs** · 08-13-2012, 03:38 PM

Hello BobbyS, welcome to Excel Forum, it needs to be like this

=AVERAGEIFS(E:E,G:G,R2,A:A,"<>"&H2 )

**BobbyS** · 08-13-2012, 04:07 PM

Thank you for your swift response daddylonglegs! The error's gone

On a very similar note, would you happen to know why =AVERAGEIFS(E:E,G:G, R2,G2, "<>"&R2+4 ) is producing a #VALUE! error if I'm trying to compute an average as long as the value in G2 does not exceed R2 by 4?

**Ace_XL** · 08-13-2012, 04:30 PM

Your ranges are inconsistent.

Shouldn't this read as
=AVERAGEIFS(E:E,G:G, R2,G:G,"<>"&R2+4)

**daddylonglegs** · 08-13-2012, 04:34 PM

but if the G:G value needs to be between R2 and R2+4 perhaps you want

=AVERAGEIFS(E:E,G:G,">="&R2,G:G,"<="&R2+4)

**BobbyS** · 08-14-2012, 10:45 AM

Thanks so much for helping guys! but by looking at the output I can see the results aren’t coming out right, so maybe I haven’t explained myself properly. Just to elaborate on what I’m doing and why:

Each row relates to an item being brought onto market. Column E has data on realised sales prices; Column G has date of exit from the market (quarter of sale); column R has date of initial listing/entry to the market. In my model, every seller should enter a market and see the average prices of items sold 4 time periods earlier. I’m asking this question about ‘does not equal’ because I do not want this average to include cases where the seller entering the market previously sold their item 4 periods before (that being included somewhere else so it would be double counting to include it in this average too). So at this point I tried the formula

=AVERAGEIFS(E:E,G:G, R2,G2, "<>"&R2+4 )

to say that if any individual unit’s date of entry exceeded the previous date of sale for that same unit by 4 quarters, do not include that previous sale price in the average of all sales prices at time t-4 (ie. if G2 is exactly 4 bigger than R2, drop it from the average). By changing the formula to

=AVERAGEIFS(E:E,G:G, R2,G:G, "<>"&R2+4 )

Am I not asking something different to the above question? Also, I noticed that the output of the new formula didn’t have any differences to the results when I ignore the double counting issue, which I know is present when I look at the data.