In Excel, AverageIf miscalculates the data I want when I use the range B:B instead of B2:B21, even though nothing else follows B21 (the column is empty after that).

The average when I use B:B as the range(not the sum range) is 747, but it is 902 when I use the specific range B2:B21. All other parts of the formula are the same.

=AVERAGEIF(B:B,"NY",D2:D21) 

versus

=AVERAGEIF(B2:B21,"NY", D2:D21) 

The purpose is to calculate average sales for New York salespersons.

CodePudding user response：

to not get false positives all ranges should be the same size and shape:

=AVERAGEIF(B:B,"NY",D:D)

With your formula it is comparing B1 but returning D2 so the results will be one row off.

It is the same as doing:

=AVERAGEIF(B1:B20,"NY", D2:D21)

So if B2 = NY the formula will use D3 not D2 in the output.